Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
R
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;
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;
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.
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
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.