topic ApplyMap - How do I get the last record in App Development

ApplyMap - How do I get the last record

magnusrydberg — Sat, 16 Nov 2024 21:11:43 GMT

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;

Re: ApplyMap - How do I get the last record

Somasundaram — Fri, 22 Mar 2019 12:14:01 GMT

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.

Re: ApplyMap - How do I get the last record

sunny_talwar — Fri, 22 Mar 2019 12:17:34 GMT

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;

Re: ApplyMap - How do I get the last record

magnusrydberg — Fri, 22 Mar 2019 15:23:27 GMT

Thanks a lot!
It works!
// Magnus