6 Replies Latest reply: Oct 18, 2017 9:49 AM by Jason Turner RSS

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

    Jason Turner

      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.