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: 
rileymd88
Contributor III
Contributor III

Looking up latest valid value using ApplyMap

Hello,

I have an Excel pricing file which looks like this:

PriceTable:

IDDatePrice
10000347401/10/2015300
10000347501/09/2015200

And then I have some data in a table which looks like this

QtyTable:

IDDateQty
10000347401/10/201510
10000347501/10/201510

What I would like to do is make a 4th column in my QtyTable which would calculate the correct value of my quantity. Ideally if there is a match between the ID&Date fields then use that price however if this lookup fails then I would like to take the latest available date for the ID.

Example Row 1) When looking at Row 1 of my QtyTable I would expect to return the price 300 and therefore the 4th column should be 10*300 = 3000 since the ID&Date combination can be matched exactly across both the PriceTable and QtyTable

Example Row 2) When looking at Row 2 of my QtyTable I would expect for this first lookup to fail as the ID&Date combination does not exist in the PriceTable but it should then revert to assigning the latest available price which is 200 in this case so the 4th column should be 10*200 = 2000

Thanks,

R

1 Solution

Accepted Solutions
sunny_talwar

Try this:

Mapping:

Mapping

LOAD ID,

     //Date,

     FirstSortedValue(Qty, -Date) as Qty

FROM

[https://community.qlik.com/thread/191846]

(html, codepage is 1252, embedded labels, table is @2)

Group By ID;

Table:

LOAD ID,

     Date,

     Price

FROM

[https://community.qlik.com/thread/191846]

(html, codepage is 1252, embedded labels, table is @1);

Left Join (Table)

LOAD ID,

     Date,

     Qty

FROM

[https://community.qlik.com/thread/191846]

(html, codepage is 1252, embedded labels, table is @2);

FinalTable:

NoConcatenate

LOAD *,

  Price * Qty as Sales;

LOAD ID,

  Date,

  Price,

  If(IsNull(Qty), ApplyMap('Mapping', ID), Qty) as Qty

Resident Table;

DROP Table Table;


Capture.PNG

View solution in original post

4 Replies
sunny_talwar

Try this:

Mapping:

Mapping

LOAD ID,

     //Date,

     FirstSortedValue(Qty, -Date) as Qty

FROM

[https://community.qlik.com/thread/191846]

(html, codepage is 1252, embedded labels, table is @2)

Group By ID;

Table:

LOAD ID,

     Date,

     Price

FROM

[https://community.qlik.com/thread/191846]

(html, codepage is 1252, embedded labels, table is @1);

Left Join (Table)

LOAD ID,

     Date,

     Qty

FROM

[https://community.qlik.com/thread/191846]

(html, codepage is 1252, embedded labels, table is @2);

FinalTable:

NoConcatenate

LOAD *,

  Price * Qty as Sales;

LOAD ID,

  Date,

  Price,

  If(IsNull(Qty), ApplyMap('Mapping', ID), Qty) as Qty

Resident Table;

DROP Table Table;


Capture.PNG

swuehl
MVP
MVP

I think you want to find a match in price table with the latest date <= the date in the qty table, so a fixed mapping based on  ID only will not work for a history of qty data.

HIC has blogged a lot about these problems and also created some tech docs.

Generating Missing Data In QlikView

Here you find several approaches including generating the missing price records in the data model and INTERVALMATCH approaches.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

You might also want to look at this sample:

Qlikview Cookbook: Expand A Pricing Date Table http://qlikviewcookbook.com/recipes/download-info/expand-a-pricing-date-table/

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

rileymd88
Contributor III
Contributor III
Author

Thanks for your help. I ended up fixing this by creating a lookup key of ID&Price and looking this up and then if it failed I would apply another ApplyMap against a sorted list which always shows the latest dates and prices so the latest price is always applied.