Discussion board where members can get started with Qlik Sense.
I am trying to match dates between two different tables.
I have one table with the following fields:
and second table with :
The data in these tables is , as an example :
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
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.
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
[@1] AS [ID],
Date#([@2],'D/M/YYYY') AS [Date1]
(txt, codepage is 1252, no labels, delimiter is '\t', msq);
//join date2s to date1s based on ID
outer join (Transform1)
Date#([@2],'DD/MM/YYYY') AS [Date2]
//Filter out records where Date2 >= Date1
where Date2 < Date1;
drop table Transform1;
//Now take the maximum Date2s (per ID) remaining from the filter
Max(Date2) as Date2
group by ID;
drop table Transform2;
//rejoin the Date1s to the remaining Date2s
outer join (Dates)
was going to say with more thought we could probably make this less convoluted.