Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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.

2 Replies
Employee
Employee

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

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

Employee
Employee

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

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