Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have and inventory file that I load that keeps track if someone counted there inventory. And if they don't count their inventory for a day it does not have a record added for that day for that location. How would I add a record of 0 for a missing day by location?
Inventory:
LOAD
Date([DateCount],'M/D/YYYY') & ' - ' & [DWSTORE] as StoreDateKey,
Date([DateCount],'M/D/YYYY') AS [Count Date],
text([NUMBERCOUNT]) AS [Count Number],
Store,
From \$(vYear)_Inventory.qvd (qvd);
example for store 10 in May they did not count on May 22, 29 or 30 so no records for them on that data show up but I would like record to show up with a 0 for the Count Number for that store.
Hi Sally,
So everyday you want to add records for each store and those records will have a date, a Store ref and a count. Start off making a table with a date field and a store ref, DWSTORE for say May 29.
Date | DWSTORE |
29-May | 1 |
29-May | 2 |
29-May | 3 |
29-May | 4 |
29-May | 5 |
Suppose when you get your count results store 3 is missing:
DWSTORE | Count |
1 | 58 |
2 | 37 |
4 | 45 |
5 | 76 |
Load the first table into Qlikview, call it DayInventory.
Load the second table as a mapping table:
MappingResults:
MAPPING
LOAD
DWSTORE,
Count
From ...ResultSource
Now
Left Join(DayInventory)
Load
DWSTORE,
Applymap('MappingResults',DWSTORE,0) as Count
Resident DayInventory;
Now we'll have DayInventory as
Date | DWSTORE | Count |
29-May | 1 | 58 |
29-May | 2 | 37 |
29-May | 3 | 0 |
29-May | 4 | 45 |
29-May | 5 | 76 |
The third argument in the ApplyMap function, 0, told QlikView to insert a default value of zero if no Count value was available.
Hopefully this will point you in the right direction.
Regards
Andrew
Thanks for pointing me in the right direction.
When I do this all I get back is all 0 for the count. Any ideas why?