Finding the last entry from a subset of a table in a load script

[smarties]

Hi,

I have a Part Table, and a Sales History Table. When loading the Part Table I want to add a new field with the last Order Year. If I could use set analysis with the FirstSortedValue function I'd be off and running. But I can't, and I don't have enough knowledge to see an alternative answer quickly. Help would be appreciated.

Thanks!

SalesHistory:
Load
    PartNumber as PreviouslySoldPart,
    Date(OrderDate, 'YYYY/MM/DD') as OrderDate,
    Year(OrderDate) as OrderYear
From [Sales.qvd] (qvd);

Parts:
Load
    PartID,
    Type,
    Description,
    FirstSortedValue( {<some filter using PartID>}, OrderYear, -OrderYear) as LastSale
From [Parts.qvd] (qvd);

Drop Table SalesHistory;

[rubenmarin · Accepted solution]

Hi, set analysis is for front-end expressions, set analysis doesn't works on script.

Which field creates the relationship between the tables? PartNumber is the same as PartID? I that case you can use a mapping table from the sales qvd and apply it on the Parts table, like:

mapLastYearByPart:
Mapping LOAD PartNumber, Max(Year(OrderDate))
From [Sales.qvd](qvd)
Group By PartNumber;

Parts:
LOAD
  PartID,
  Type,
  Description,
  Applymap('mapLastYearByPart',PartID,Null()) as LastSale
From [Parts.qvd](qvd);
  

[smarties]

Thanks @rubenmarin. ApplyMap it is. All working.