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]

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;

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

[Anil_Babu_Samineni]

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

So, You can think like

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

[ckchouk123]

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]

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

[ckchouk123]

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:

[Vegar]

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]

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?