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

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

Anonymous — Wed, 18 Oct 2017 08:36:01 GMT

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 [

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.

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

antoniotiman — Wed, 18 Oct 2017 09:01:48 GMT

Hi Jason,

try this

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

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

Regards,

Antonio

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

Anonymous — Wed, 18 Oct 2017 09:25:39 GMT

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:

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

antoniotiman — Wed, 18 Oct 2017 09:31:08 GMT

Jason,

this is in Front End, not in script.

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

Anonymous — Wed, 18 Oct 2017 09:34:29 GMT

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)

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

antoniotiman — Wed, 18 Oct 2017 09:52:06 GMT

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;

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

Anonymous — Wed, 18 Oct 2017 13:49:38 GMT

this worked perfectly for what i needed - thanks