topic Mapping load : search for record with latest date in QlikView

Mapping load : search for record with latest date

andries_bos — Thu, 08 Mar 2012 09:15:28 GMT

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.

Mapping load : search for record with latest date

CELAMBARASAN — Thu, 08 Mar 2012 09:26:21 GMT

Hi,

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

Celambarasan

Mapping load : search for record with latest date

andries_bos — Thu, 08 Mar 2012 09:30:17 GMT

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);

Re: Mapping load : search for record with latest date

CELAMBARASAN — Thu, 08 Mar 2012 09:32:26 GMT

Hi,

Check with this

Load

Article_ID,

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

Resident Invoices Group By Article_ID;

Hope it helps

Celambarasan

Re: Mapping load : search for record with latest date

CELAMBARASAN — Thu, 08 Mar 2012 09:44:24 GMT

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