Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Customer | Date2 | Most Recent Date Prior to Date 2 from Lookup | Product @ this date |
---|---|---|---|
Astrida | 01/02/2017 | 01/01/2017 | AA |
Betacab | 11/02/2017 | 10/02/2017 | AA |
Canutility | 01/04/2017 | 01/04/2017 | BB |
Divadip | 03/04/2017 | 01/04/2017 | AA |
i've tried using FirstSortedValue but i cannot build in the part where it ignores any dates greater than the Date2 to lookup from.
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;
Hi Jason,
try this
FirstSortedValue(DISTINCT Product,-(Aggr(If(Date2 >= Date,Date),Customer,Date)))
Max(Aggr(If(Date2 >= Date,Date),Customer,Date))
Regards,
Antonio
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:
Jason,
this is in Front End, not in script.
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)
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;
this worked perfectly for what i needed - thanks