How to match dates between two different tables - qlik Sense

[#1]

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:

23	1/1/2013
78	1/2/2013
78	25/02/2013
88	3/3/2013
88	5/3/2013

2:

23	31/12/2012
23	31/01/2013
23	28/02/2013
23	31/03/2013
23	30/04/2013
23	31/05/2013
78	31/12/2012
78	31/01/2013
78	28/02/2013
78	31/03/2013
78	30/04/2013
78	31/05/2013
88	31/12/2012
88	31/01/2013
88	28/02/2013
88	31/03/2013
88	30/04/2013
88	31/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

23	1/1/2013	31/12/2012
78	1/2/2013	31/01/2013
78	25/02/2013	31/01/2013
88	3/3/2013	28/02/2013
88	5/3/2013	28/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.

[JonnyPoole]

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);

.

[JonnyPoole]

was going to say with more thought we could probably make this less convoluted.