Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm using the ApplyMap function to get a record from the order timelog line using the order number.
The ordertimelog file contains many records for each order and I want to get the last one.
My ApplyMap works but I always get the first ordertimelog record in sequence instead of the last one.
How should I set up the sqript to get the last record from the Timelog file?
This is how my sqript looks today:
MapReportedByToOrder:
MAPPING LOAD StringKey1 as TaskOrderNumber,
StringKey5 as TaskReportedBy;
SQL SELECT * FROM "MyL_PROD_220".OTSDATA.TimeLog;
-------------------------
LOAD LONumber as OrderNumber,
Network,
ApplyMap('MapReportedByToOrder', LONumber, null()) as OrderReportedBy;
SQL SELECT *FROM "MyL_PROD_220".OTSDATA.LOHeader;
You have two options
1) Use Group By to bring the max TaskReportedBy
MapReportedByToOrder: MAPPING LOAD StringKey1 as TaskOrderNumber, Max(StringKey5) as TaskReportedBy Group By StringKey1; SQL SELECT * FROM "MyL_PROD_220".OTSDATA.TimeLog;
2) Use Order by to sort TaskReportedBy in descending order
MapReportedByToOrder: MAPPING LOAD StringKey1 as TaskOrderNumber, StringKey5 as TaskReportedBy; SQL SELECT * FROM "MyL_PROD_220".OTSDATA.TimeLog ORDER BY StrinkKey5 desc;
Hi,
Using applymap() we can retrieve very first record only.
You can use peek(0 function to get the table values based on the positions.
You have two options
1) Use Group By to bring the max TaskReportedBy
MapReportedByToOrder: MAPPING LOAD StringKey1 as TaskOrderNumber, Max(StringKey5) as TaskReportedBy Group By StringKey1; SQL SELECT * FROM "MyL_PROD_220".OTSDATA.TimeLog;
2) Use Order by to sort TaskReportedBy in descending order
MapReportedByToOrder: MAPPING LOAD StringKey1 as TaskOrderNumber, StringKey5 as TaskReportedBy; SQL SELECT * FROM "MyL_PROD_220".OTSDATA.TimeLog ORDER BY StrinkKey5 desc;