ApplyMap - How do I get the last record

[magnusrydberg]

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;

[Somasundaram]

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.

[sunny_talwar · Accepted solution]

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;

[magnusrydberg]

Thanks a lot!
It works!
// Magnus