2 Replies Latest reply: Jun 28, 2017 9:02 AM by Bala Bhaskar RSS

    How to compare two fields from two different tables ?

    timothee aubourg

      Hello,

       

      I'm new to Qlik View, and I have a scripting question : I want to filter a field from a table according an other field in an other table. In other words :

       

      I have :

      Table A
      Key_A_B
      Date_A

       

      Table B
      Key_A_B
      Date_B

       

      And I want to modify Table A as follow :

      Table A
      Key_A_B
      Date_A
      New_Date_B

      and New_Date_B is Date_B  where (Date_B < Date_A).

       

      Currently, to do that, my script is very ugly and not optimized ! It's something like this :

      -----------------------------------------------------------------------------------------------------------------------

      // Step 1

      Left Join([Table A])

      Load *

      Resident([Table B]);

       

      // So at this step, I have Date_A and Date_B Table A.
      // Now, I filter Date_B by Date_A to obtain New_Date_B

       

      // Step 2

      Right Join ([Table A])

      Load

          Date_B as New_Date_B

      Resident([Table A])

      where(Date_B < Date_A);

      -----------------------------------------------------------------------------------------------------------------------


      But this last step is very ugly, i don't find it very powerful (it takes too much time in script loading which is not a positive sign!)

      In fact, i would like to do it in a more simply way (may be in one step), but to do that I need to directly compare Table A with Table B.
      I tried something like that :

       

      Left Join([Table A])

      Load

           Date_B as New_Date_B

      Resident([Table B])

      where(Date_B < Date_A);


      But it doesn't work because of Date_B which is not loading yet in Table A.
      Do you have an idea to do it ?


      Thanks !