3 Replies Latest reply: Oct 11, 2018 3:36 AM by Juraj Misina RSS

    Help on if logic

    Mahitha M

      Hi Experts,

       

      Can any one please help me on below requirement.

      I have the data like below.

      Here same trade keys is in three Different Types i.e, Internal, External and Global.

       

      Requirement is when any id present in Internal, External and Global Types need to keep that id in Internal  Type

      If there Is no Internal then need to keep in External else in Global.

       

      Please help me on this. Thanks in advance

       

      Input :

      Key                 Type            Identfier

      1578693       Internal          afv

      1578693       External        sf_erkil

      1578693       Global           huk_lop

      1578983        Internal         afv

      1578983       External        sg_kilo

      1578988        Global           hjkl

      1578988       External        siklo

      15789698      Global           olp


      Expected Output:

      Key                 Type            Identfier

      1578693       Internal          afv

      1578983        Internal         afv

      1578988       External        siklo

      15789698      Global           olp



        • Re: Help on if logic
          Stefan Wühl

          Maybe like

           

          Output:

          LOAD *, Key as Keycheck RESIDENT Input

          WHERE Type = 'Internal';

          CONCATENATE

          LOAD *, Key as Keycheck RESIDENT Input

          WHERE Type = 'External'  and not exists(Keycheck,Key);

          CONCATENATE

          LOAD * RESIDENT Input

          WHERE Type = 'Global' and not exists(Keycheck,Key);

           

          DROP FIELD Keycheck;

            • Re: Help on if logic
              Stefan Wühl

              Another option would be to assign type a dual value with a numeric sort order value, then LOAD your input table like this:

               

              NOCONCATENATE

              LOAD     

                   Key,

                   MinString(Type) as Type,

                   FirstSortedValue(Identifier, Type) as Identifier

              RESIDENT Input

              GROUP BY Key;

            • Re: Help on if logic
              Juraj Misina

              Hi,

              this will not be if logic, rather data load logic.

               

              Input:
              LOAD
              Key,
              Type,
              Identifier,
              If(Type='Internal', Key)  as _internalKeys,
              If(Type='External', Key)  as _externalKeys
              inline [
              Key,                Type,            Identfier
              1578693,      Internal,          afv
              1578693,      External,        sf_erkil
              1578693,      Global,          huk_lop
              1578983,        Internal,        afv
              1578983,       External,        sg_kilo 
              1578988,        Global,          hjkl
              1578988,       External,        siklo
              15789698,      Global,          olp
              ];
              
              
              Output:
              LOAD distinct
                Key,
                Key    as _processedKey,
                Type,
                Type,
                Identifier
              Resident Input
              Where
                Not(IsNull(_internalKeys))
              ;
              
              
              Concatenate(Output)
              LOAD distinct
                Key,
                Key    as _processedKey,
                Type,
                Type,
                Identifier
              Resident Input
              Where
                Not(IsNull(_externalKeys))
                and
                Not(Exists(_processedKey, _externalKeys))
              
              ;
              
              
              Concatenate(Output)
              LOAD distinct
                Key,
                Key    as _processedKey,
                Type,
                Type,
                Identifier
              Resident Input
              Where
                Not(Exists(_processedKey, Key))
              
              ;
              
              
              Drop Field _processedKey;
              
              


              Hope this helps.

              Juraj