Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Value related to Most Recent Date in other data set before date

Hello all,

I'd like to get a dataset to look up the most recent value relating to a dataset comparing dates and pick the most recent occurance before the date i specify.. heres two data sets for examples:

Lookup:

LOAD * inline [

Customer|Product|OrderNumber|UnitSales|CustomerID|Date

Astrida|AA|1|10|1|01/01/2017

Astrida|AA|7|18|1|01/01/2016

Astrida|BB|4|9|1|01/05/2016

Astrida|CC|6|2|1|12/02/2017

Betacab|AA|5|4|2|10/02/2017

Betacab|BB|2|5|2|01/02/2017

Betacab|DD|12|25|2|01/03/2017

Canutility|BB|3|8|3|01/04/2017

Canutility|CC|13|19|3|01/03/2017

Divadip|AA|9|16|4|01/04/2017

Divadip|AA|10|16|4|01/05/2016

Divadip|DD|11|10|4|01/03/2017

] (delimiter is '|');

Temp:

LOAD * inline [

Customer|Date2

Astrida|01/02/2017

Betacab|11/02/2017

Canutility|01/04/2017

Divadip|03/04/2017

] (delimiter is '|');

I would like to lookup using Temp table Date2 the most recent values in the lookup table, so my expected results would be:

CustomerDate2Most Recent Date Prior to Date 2 from LookupProduct @ this date
Astrida01/02/201701/01/2017AA
Betacab11/02/201710/02/2017AA
Canutility01/04/201701/04/2017BB
Divadip03/04/201701/04/2017AA

i've tried using FirstSortedValue but i cannot build in the part where it ignores any dates greater than the Date2 to lookup from.

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

Script Version

Lookup:
LOAD * inline [
Customer|Product|OrderNumber|UnitSales|CustomerID|Date
Astrida|AA|1|10|1|01/01/2017
Astrida|AA|7|18|1|01/01/2016
Astrida|BB|4|9|1|01/05/2016
Astrida|CC|6|2|1|12/02/2017
Betacab|AA|5|4|2|10/02/2017
Betacab|BB|2|5|2|01/02/2017
Betacab|DD|12|25|2|01/03/2017
Canutility|BB|3|8|3|01/04/2017
Canutility|CC|13|19|3|01/03/2017
Divadip|AA|9|16|4|01/04/2017
Divadip|AA|10|16|4|01/05/2016
Divadip|DD|11|10|4|01/03/2017
]
(delimiter is '|');

Left Join
LOAD * inline [
Customer|Date2
Astrida|01/02/2017
Betacab|11/02/2017
Canutility|01/04/2017
Divadip|03/04/2017
]
(delimiter is '|');

Inner Join
LOAD Customer,Max(If(Date2 >= Date,Date)) as Date
Resident Lookup
Group By Customer
;

View solution in original post

6 Replies
antoniotiman
Master III
Master III

Hi Jason,

try this

FirstSortedValue(DISTINCT Product,-(Aggr(If(Date2 >= Date,Date),Customer,Date)))

Max(Aggr(If(Date2 >= Date,Date),Customer,Date))

Regards,

Antonio

Anonymous
Not applicable
Author

Hi Antonio - thanks for your help - i think it isnt working currently as the tables are not linked, but see the error below. How would you link the two, i initially was planning on doing an ApplyMap or something or a FirstSortedValue, but as it's looking across tables im not sure its working.

see below:

Capture.PNG

antoniotiman
Master III
Master III

Jason,

this is in Front End, not in script.

Anonymous
Not applicable
Author

Hi, is there a scripted version or a solution for script as i would like to basically apply the most recent product type and append the two tables together using the most recent date?

My ideal output would be as per the table above as i would like to use this across data historically to say what product somebody was on at the time of an event (basically the lookup table will be a historical information table for each customer, but there will be changes over time to product or other values)

antoniotiman
Master III
Master III

Script Version

Lookup:
LOAD * inline [
Customer|Product|OrderNumber|UnitSales|CustomerID|Date
Astrida|AA|1|10|1|01/01/2017
Astrida|AA|7|18|1|01/01/2016
Astrida|BB|4|9|1|01/05/2016
Astrida|CC|6|2|1|12/02/2017
Betacab|AA|5|4|2|10/02/2017
Betacab|BB|2|5|2|01/02/2017
Betacab|DD|12|25|2|01/03/2017
Canutility|BB|3|8|3|01/04/2017
Canutility|CC|13|19|3|01/03/2017
Divadip|AA|9|16|4|01/04/2017
Divadip|AA|10|16|4|01/05/2016
Divadip|DD|11|10|4|01/03/2017
]
(delimiter is '|');

Left Join
LOAD * inline [
Customer|Date2
Astrida|01/02/2017
Betacab|11/02/2017
Canutility|01/04/2017
Divadip|03/04/2017
]
(delimiter is '|');

Inner Join
LOAD Customer,Max(If(Date2 >= Date,Date)) as Date
Resident Lookup
Group By Customer
;

Anonymous
Not applicable
Author

this worked perfectly for what i needed - thanks