2 Replies Latest reply: Nov 11, 2015 1:26 PM by Jonathan Poole RSS

    How to match dates between two different tables - qlik Sense

      Hi everybody,

       

      I am trying to match dates between two different tables.

      I have one table with the following fields:

      Inv_dates:

       

      Part_Part,

      Inv_date

       

      and second table with :

       

      Cost_cal_date:

       

      Part ,

      Cost_cal_date

       

      The data in these tables is , as an example :

       

      1:

      231/1/2013
      781/2/2013
      7825/02/2013
      883/3/2013
      885/3/2013

      2:

         

      2331/12/2012
      2331/01/2013
      2328/02/2013
      2331/03/2013
      2330/04/2013
      2331/05/2013
      7831/12/2012
      7831/01/2013
      7828/02/2013
      7831/03/2013
      7830/04/2013
      7831/05/2013
      8831/12/2012
      8831/01/2013
      8828/02/2013
      8831/03/2013
      8830/04/2013
      8831/05/2013

       

      What I am trying to do is to on the first table, for each line, the most close COST_CAL_DATE from the second table but which is earlier than the date on the line. for example

       

          

      231/1/201331/12/2012
      781/2/201331/01/2013
      7825/02/201331/01/2013
      883/3/201328/02/2013
      885/3/201328/02/2013

       

       

      I have tried to first join both tables, with left join and than to read from the same table for a new one with

      'where max(COST_CAL_DATE) <= INV_DATE' but it didn't work.

       

      I also tried to work with the INTERVALMATCH function but it didn't work.

       

      I will appreciate any help.

       

      Thank You,

       

      Osher Sassoni.

        • Re: How to match dates between two different tables - qlik Sense
          Jonathan Poole

          I'm not sure you can do a max() and bring in ID, Date1, and Date2 in the same step so i found i had to break up the steps. With more thought

           

          //load Date1s

           

          Transform1:

          LOAD

            [@1] AS [ID],

            Date#([@2],'D/M/YYYY') AS [Date1]

          FROM [lib://Temp/date1.txt]

          (txt, codepage is 1252, no labels, delimiter is '\t', msq);

           

          //join date2s to date1s based on ID

           

          outer join (Transform1)

          LOAD

            [@1] AS [ID],

            Date#([@2],'DD/MM/YYYY') AS [Date2]

          FROM [lib://Temp/date2.txt]

          (txt, codepage is 1252, no labels, delimiter is '\t', msq);

           

           

          //Filter out records where Date2 >= Date1

           

          noconcatenate

          Transform2:

          load

            ID,

              Date1,

              Date2

          resident Transform1

          where Date2 < Date1;

           

          drop table Transform1;

           

          //Now take the maximum Date2s (per ID)  remaining from the filter

          Dates:

          Load

            ID,

              Max(Date2) as Date2

          resident Transform2

          group by ID;

           

          drop table Transform2;

           

          //rejoin the Date1s to the remaining Date2s

          outer join (Dates)

          LOAD

            [@1] AS [ID],

            Date#([@2],'D/M/YYYY') AS [Date1]

          FROM [lib://Temp/date1.txt]

          (txt, codepage is 1252, no labels, delimiter is '\t', msq);

          .

           

          Capture.PNG