Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

jason_turner
New Contributor

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
Honored Contributor III

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

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
;

6 Replies
antoniotiman
Honored Contributor III

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

Hi Jason,

try this

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

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

Regards,

Antonio

jason_turner
New Contributor

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

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
Honored Contributor III

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

Jason,

this is in Front End, not in script.

jason_turner
New Contributor

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

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
Honored Contributor III

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

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
;

jason_turner
New Contributor

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

this worked perfectly for what i needed - thanks