topic Re: Loading empty records when a day is missing in QlikView

Loading empty records when a day is missing

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Loading empty records when a day is missing

effinty2112 — Wed, 26 Jul 2017 21:20:37 GMT

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

Re: Loading empty records when a day is missing

Anonymous — Thu, 27 Jul 2017 16:34:40 GMT

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?