topic Re: Looking up latest valid value using ApplyMap in QlikView

Looking up latest valid value using ApplyMap

rileymd88 — Tue, 17 Nov 2015 22:13:48 GMT

Hello,

I have an Excel pricing file which looks like this:

PriceTable:

ID	Date	Price
100003474	01/10/2015	300
100003475	01/09/2015	200

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

QtyTable:

ID	Date	Qty
100003474	01/10/2015	10
100003475	01/10/2015	10

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,

Re: Looking up latest valid value using ApplyMap

sunny_talwar — Tue, 17 Nov 2015 22:22:01 GMT

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;

Re: Looking up latest valid value using ApplyMap

swuehl — Tue, 17 Nov 2015 22:36:58 GMT

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.

Re: Looking up latest valid value using ApplyMap

rwunderlich — Wed, 18 Nov 2015 01:21:38 GMT

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

Re: Looking up latest valid value using ApplyMap

rileymd88 — Fri, 27 Nov 2015 08:49:10 GMT

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.