Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ckchouk123
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
Vegar
MVP
MVP

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.

View solution in original post

15 Replies
Vegar
MVP
MVP

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;
Thiago_Justen_

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
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
PrashantSangle

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.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Anil_Babu_Samineni

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

So, You can think like

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
ckchouk123
Contributor III
Contributor III
Author

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!
ckchouk123
Contributor III
Contributor III
Author

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

ckchouk123
Contributor III
Contributor III
Author

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

 

Vegar
MVP
MVP

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.
ckchouk123
Contributor III
Contributor III
Author

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?