Mapping load : search for record with latest date

[andries_bos]

I do have a tabel containing all invoice prices for articles;

I would like to combine this 'latest/current price' in my main tabel containing all articles. This in the script, not in my presentation layer.

I use a 'mapping load' where I an searching on article number and returning the price. However this would return the first available record for this article.

How could I use a mapping where a 'date' parameter should also be applied  : search on 'latest'

in code:

mapLatestPrice

mapping Load

      Article_ID

     Price

resident Invoices;

mapLatestDate

mapping Load

      Article_ID

     Price

resident Invoices;

ApplyMap ('mapLatestPrice',Article,'0') AS Articel.LatestPrice,

ApplyMap ('mapLatestDate',Article,'') AS Articel.LatestDate,

the invoices table:

article	Price	date
1002	12,5	1-1-2012
1002	13,5	31-1-2012
1002	10	30-6-2011

the second record should obvious be the result and not the first record.

[CELAMBARASAN]

Hi,

     Is your problem is to get the latest price of the article?

Celambarasan

[andries_bos]

Yes. 

Is it in some way possible to add a search 'filter' to mapping load e.g.

mapLatestPrice

mapping Load

      Article_ID

     Price

resident Invoices where highest(date);

[CELAMBARASAN]

Hi,

     Check with this

     Load

          Article_ID,

          FirstSortedValue(Price,-Num(Date)) as Price

     Resident Invoices Group By Article_ID;

Hope it helps

Celambarasan

[CELAMBARASAN · Accepted solution]

Hi,

     The above script will give you a null value if there are more records of article with same date.

     If possible order your Invoice table in the way with the date and article id.

     Load

          *

     Resident Invoices Order by Article_ID asc,Date desc;

     Then you can write Script as

      Load

          Article_ID,

          FirstValue(Price) as Price

     Resident Invoices Group By Article_ID;

Hope it helps

Celambarasan