topic Re: Fill missing Month-Year Data using Qlik Sense Load Script in App Development

Fill missing Month-Year Data using Qlik Sense Load Script

rjn — Mon, 16 Mar 2020 20:55:46 GMT

Dear All,

I am facing a issue with missing values in Qlik Sense Pivot Table.

The requirement is to replace the missing values (-) with Zero (0), since there is no provision in front end to fix the issue. Need suggestions/Advise that can be fixed using the load script.

I used the Master calendar, where I have Month-Year from 2018 to 2020, when I add the month in Pivot Table Column, the missing values shows as (-), I wanted to replace them with Zero (0).

Please Help.

Re: Fill missing Month-Year Data using Qlik Sense Load Script

Wlad_Masi — Thu, 23 Apr 2020 21:31:47 GMT

Hi @rjn ,

If you use Set NullValue = 'NULL'; on your load script, does it work for you?

Re: Fill missing Month-Year Data using Qlik Sense Load Script

rjn — Wed, 13 May 2020 07:55:03 GMT

Hi Wlad_Masi,

I tired with Set NullValue = 'NULL'; , doesn't work.

Re: Fill missing Month-Year Data using Qlik Sense Load Script

marcel_olmo — Wed, 13 May 2020 08:36:20 GMT

Hi @rjn ,

my current idea is a little messy but I think this is the only possible workaround.

First you have to create a temporal master table with the dimensions you want to add into the pivot table and the yearmonth associated value.

After that, you have to create a fake master calendar with the same combinations of dimensions and all the possibilities of year month.

Finally do the substraction of both tables to get the values you have left to add a 0 value, and then concatenate it with your original sales data.

Hope this will help.

Best regards, Marcel.