Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

andriesb
Not applicable

Mapping load : search for record with latest date

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:

articlePricedate
100212,51-1-2012
100213,531-1-2012
10021030-6-2011

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

1 Solution

Accepted Solutions
celambarasan
Not applicable

Re: Mapping load : search for record with latest date

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

4 Replies
celambarasan
Not applicable

Mapping load : search for record with latest date

Hi,

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

Celambarasan

andriesb
Not applicable

Mapping load : search for record with latest date

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
Not applicable

Re: Mapping load : search for record with latest date

Hi,

     Check with this

     Load

          Article_ID,

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

     Resident Invoices Group By Article_ID;

Hope it helps

Celambarasan

celambarasan
Not applicable

Re: Mapping load : search for record with latest date

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