Show 0 for missing dates

[ckchouk123]

Please see attached for desired and current result I am getting when trying to show 0 for all dates with missing occurences for a specific site.

This is my current load script:

Noconcatenate

NewTable:
Load   DateKey
Resident Calendar;

Left Join (NewTable)
Load
    DateKey,
    IF(LEN(Occurence)='-', 0, Occurence) AS OccurenceCount,
    Site

Resident Table1;

[Vegar · Accepted solution]

It can be done in different ways, a straight forward way is to loop through your dates and Sites. After you have created your Transaction table do this.


FOR each vSite in FieldValueList('Site')
FOR Each vDateKey in FieldValueList('DateKey')
Concatenate (Transactions)
LOAD
'$(vDateKey)' as DateKey ,
'$(vSite)' as Site,
0 as Occurence

Autogenerate 1;
NEXT vDateKey
Next vSite

You can make this loop more sophisticated if you please,excluding date-site combinations that do exist, but I think this will do the trick that you are looking for.

[ckchouk123]

This worked great! Would you please explain to me how this loop actually works? I am a SQL guy and fairly new to Qlik Sense. Also, how would I exclude existing dates with values as you said?

[Vegar]

I'll try to explain.

The two FOR EACH ... IN loops through every unique value of Site and DateKey in your data model.
The CONCATENATE forces the following LOAD to be added into your Transaction table.

'$(vDateKey)' is the content of the looped DateKey and '$(vSite)' is the content of the looped Site.

AUTOGENERATE tells us how many rows that is created.

The NEXT iterates the next value in the loop.

[Vegar]

A solution for excluding for existing date-store combinations.

1. Add the following field into your transaction table:
Hash256(Site,DateKey) as SiteDateKey

2. Adjust your zero transaction load statement like this.
LOAD
'$(vDateKey)' as DateKey ,
'$(vSite)' as Site,
0 as Occurence
Autogenerate 1
WHERE
NOT EXISTS(SiteDateKey, Hash256('$(vSite)' ,'$(vDateKey)' )
;

3. Drop the SiteDateKey field before exiting the load script:

DROP FIELD SiteDateKey;

Edit: changed EXISTS to NOT EXISTS

[ckchouk123]

Thank you Vegar, is it "EXISTS" or "NOT EXISTS"?

[Vegar]

My bad, it should off course be NOT EXISTS. Thanks for asking.