topic Re: Show 0 for missing dates in App Development

Show 0 for missing dates

ckchouk123 — Mon, 19 Nov 2018 20:52:03 GMT

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;

Re: Show 0 for missing dates

Vegar — Mon, 19 Nov 2018 22:48:05 GMT

I made adjustments to your OccurenceCount expression and presented an alternative solution using the alt() function.

NewTable:
Load DateKey
Resident Calendar;

Left Join (NewTable)
Load
DateKey,
IF(LEN(Occurence)>0, Occurence,0) AS OccurenceCount,
Alt(Occurence, 0) as OccurenceCount 2,
Site

Resident Table1;

Re: Show 0 for missing dates

Thiago_Justen_ — Tue, 20 Nov 2018 01:41:04 GMT

You can also try something like this:

NewTable:
Load DateKey
Resident Calendar;

Left Join (NewTable)
Load
DateKey,
IF(Match(Occurence,'-') or Occurence='-' or IsNull (Occurence) or Len(Trim (Occurence))=0, 0, Occurence) AS
OccurenceCount,
Site
Resident Table1;

Re: Show 0 for missing dates

PrashantSangle — Tue, 20 Nov 2018 04:07:23 GMT

where do you want it??

1: Front end (UI)

modify your expression

if(trim(Occurance)='-',0,Occurance)

2: In Back End (Script)

NewTable:
Load DateKey
Resident Calendar;

Left Join (NewTable)
Load
DateKey,
Occurence AS OccurenceCount,
Site

Resident Table1;

NoConcatenate

Final:

Load DateKey,

if(isnull(OccurenceCount) or len(trim(OccurnaceCount))=0,'0',OccuranceCount) as OccuranceCount,

Site

Resident NewTable;

Drop table NewTable;

Re: Show 0 for missing dates

Anil_Babu_Samineni — Tue, 20 Nov 2018 04:22:27 GMT

Len(Occurrence) always return number format like 0,1...

So, You can think like

IF(IsNull(Occurence), 0, Occurence) AS OccurenceCount,

Re: Show 0 for missing dates

ckchouk123 — Tue, 20 Nov 2018 14:20:36 GMT

Thank you for the response. I just realized that I did not include the following in my post that is also part of my load script:

NullASValue *;

Set NullValue = '-';

I have also tried your suggestion, but I am still not getting the desired output.

Thank you!

Re: Show 0 for missing dates

ckchouk123 — Tue, 20 Nov 2018 14:33:16 GMT

Thank you for the response to this. I have tried your suggestion, but still getting the same result.

Re: Show 0 for missing dates

ckchouk123 — Tue, 20 Nov 2018 14:51:01 GMT

Thank you for the response. I do need this in the back end. Your suggestion partially worked, I am now seeing zero for all dates where none of the sites have values, but if one site has a value at a specific date and another site does not, I am only seeing the record for the site with a value. See below more details:

Re: Show 0 for missing dates

Vegar — Tue, 20 Nov 2018 16:09:41 GMT

You do not have any transactions for your Sites the listed datekeys.
You could try checking the dimension property "show all values", it might help.

If it is important to present values for all sites every datekeys then I would suggest that you create fake zero-transactions for every Site and datekey. This will ensure you to have a site association to every datekey.

Re: Show 0 for missing dates

ckchouk123 — Tue, 20 Nov 2018 16:18:08 GMT

I know that is my problem, but asking what is the best way to handle it. My main question should really be, Do I add the fake records in the source file outside of Qlik and then load it, or is there anything I can do in the load script to create the missing records?

Re: Show 0 for missing dates

Vegar — Tue, 20 Nov 2018 19:18:44 GMT

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.

Re: Show 0 for missing dates

ckchouk123 — Tue, 20 Nov 2018 20:44:09 GMT

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?

Re: Show 0 for missing dates

Vegar — Tue, 20 Nov 2018 22:42:27 GMT

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.

Re: Show 0 for missing dates

Vegar — Wed, 21 Nov 2018 20:53:45 GMT

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

Re: Show 0 for missing dates

ckchouk123 — Wed, 21 Nov 2018 16:30:28 GMT

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

Re: Show 0 for missing dates

Vegar — Wed, 21 Nov 2018 20:52:06 GMT

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