Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andriesb
Creator II
Creator II

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
Partner - Champion
Partner - Champion

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

View solution in original post

4 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

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

Celambarasan

andriesb
Creator II
Creator II
Author

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
Partner - Champion
Partner - Champion

Hi,

     Check with this

     Load

          Article_ID,

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

     Resident Invoices Group By Article_ID;

Hope it helps

Celambarasan

CELAMBARASAN
Partner - Champion
Partner - Champion

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