Convert null values to zeros in the export

[lucas3]

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

[SunilChauhan]

For example your. Expression is sum(fieldname)

So use like 

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

Same for all expression

[lucas3]

Hi

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

[SunilChauhan · Accepted solution]

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

[vinieme12 · Accepted solution]

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