1 Reply Latest reply: Jan 29, 2015 10:10 AM by Ruben Marin RSS

    if statement with not exists

      hi guys,

      I have following problems:

      T1Seller
      1A
      2
      3C

       

      T2Seller
      qwA
      asB
      yx

       

      T1T2
      1qw
      2as
      3yx

       

      I want to show all the Seller: 1,2,3 WITHIN to fill the missing information like:

      a) if I click B, it only shows "as", not "2" from T1

      so how can I tell Qlik in Script, that:

       

      If no seller in T1, look in T2 ?

      and reversed ..

       

      Like:

      If T1 has not Seller, so look in T2 and summit that 2=as=B -> 2=B

      or

      If(Seller not exists in T1,

      I am sorry for my english

        • Re: if statement with not exists
          Ruben Marin

          Hi Thomas, maybe helps if you can assing in script T1 and T2 for each seller, this way will be easier to get the associated value, you can do this:

          // ***** Original data *****

          T1:

          LOAD * Inline [

          T1,Seller

          1,A

          2,

          3,C

          ];

           

          T2:

          LOAD * Inline [

          T2,Seller

          qw,A

          as,B

          yx  

          ];

           

          Relations:

          LOAD * Inline [

          T1,T2

          1,qw

          2,as

          3,yx

          ];

           

          // ***** Creating Mapping *****

          Map_T1:

          Mapping LOAD T1,Seller Resident T1;

           

          Map_T2:

          Mapping LOAD T2,Seller Resident T2;

           

          MAP_RelationsT1_T2:

          Mapping LOAD T1, T2 Resident Relations;

           

          MAP_RelationsT2_T1:

          Mapping LOAD T2, T1 Resident Relations;

           

          // ***** Create table by seller with T1 and T2 *****

          Result_tmp:

          NoConcatenate LOAD T1,

              If(IsNull(Seller) or Seller='', ApplyMap('Map_T2', ApplyMap('MAP_RelationsT1_T2', T1)), Seller) as Seller

          Resident T1;

           

          Concatenate LOAD T2,

              If(IsNull(Seller) or Seller='', ApplyMap('Map_T1', ApplyMap('MAP_RelationsT2_T1', T2)), Seller) as Seller

          Resident T2;

           

          Result:

          NoConcatenate LOAD Seller, MinString(T1) as T1, MinString(T2) as T2 Resident Result_tmp Group By Seller;

           

          DROP Tables T1, T2, Relations, Result_tmp;