Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Hi,
Is your problem is to get the latest price of the article?
Celambarasan
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);
Hi,
Check with this
Load
Article_ID,
FirstSortedValue(Price,-Num(Date)) as Price
Resident Invoices Group By Article_ID;
Hope it helps
Celambarasan
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