Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Show 0 for missing dates

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;

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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.
Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

15 Replies
Highlighted
Partner
Partner

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;
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Specialist III
Specialist III

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;

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
Highlighted

where do you want it??

1: Front end (UI)

modify your expression 

like

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;

 

 

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted

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

So, You can think like

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Contributor III
Contributor III

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!
Highlighted
Contributor III
Contributor III

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

Highlighted
Contributor III
Contributor III

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:

 

QlikSupport.PNG

 

Highlighted
Partner
Partner

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.
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Contributor III
Contributor III

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?