topic Re: Convert null values to zeros in the export in Reporting Service & Alerting

Convert null values to zeros in the export

lucas3 — Fri, 08 Apr 2022 10:31:45 GMT

Hi,

When I export a file from Qlik to Excel, all of my Null values are converted to ' - ' in my file, which is completely normal.

Problem is I want '0' instead of '-'. Is there a way to modify the way Qlik interprets Nulls during the extraction to convert them to '0' ?

It is impossible for me to deal with it from Excel, because with 'Ctrl+h' it also replaces the minus signs...
Thank you for your help

Lucas

Re: Convert null values to zeros in the export

SunilChauhan — Fri, 08 Apr 2022 16:33:52 GMT

For example your. Expression is sum(fieldname)

So use like

Sum( if( isnull(fieldname),0,fieldname))

Same for all expression

Re: Convert null values to zeros in the export

lucas3 — Mon, 11 Apr 2022 16:04:52 GMT

Thank you for your answer and sorry for the late reply !

I could do this but this technique implies I would have to go through all my chart again and I have around 20 columns... Isn't there a more convenient way ?

Thank you

Re: Convert null values to zeros in the export

SunilChauhan — Wed, 13 Apr 2022 10:40:18 GMT

application level we can do like below

NullAsValue A,B;

Set NullValue = 'NULL';

LOAD A,B from x.csv;

you can have any no of values comma seperated like A,B,......

NullAsValue A,B,C,D;

Set NullValue = 'NULL';

NullAsValue A,B;

Set NullValue = 'NULL';

LOAD A,B from x.csv;

Load C, D From y.csv

Re: Convert null values to zeros in the export

vinieme12 — Mon, 18 Apr 2022 07:20:21 GMT

you will have to modify your expressions, there is no other option

replacing null values to 0 or something else will not work on instances where value is null where there is no relevant data

for example in dimension table dimension1 has values a,b,c,d but fact only has data for a,b,c

in this case value for "d" will always be null in your charts, since no data exists

So modifying expressions to

if(sum(Xyz),sum(Xyz),sum(0))

is the best way

Re: Convert null values to zeros in the export

fatinfazrizanor — Thu, 19 Jun 2025 13:01:53 GMT

Hi,

i have the same issue. I know i need to edit the expression to do the if statement. But i concern about the performance of the dashboard. My expression is too long, i afraid i put the if statement, it will impact the chart to calculate the expression.
I wonder if there is any ways to do this.

regards,