Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
TF1002
New Contributor

How to convert null values to 0

Hi all,

I have a set of data with posting codes and the amount posted against them each period- I want to put these into a pivot table, however there are some periods where certain codes have nothing posted against them. When I export the data from the table, these are assigned a value of '-', rather than 0,  which messes up the template which I have to populate with this data. After researching and trying to work out how to convert these to 0, I found something about an option to set chart properties to 'populate missing cells' within Qlik View, which sounds like it would be ideal, but there doesn't appear to be an equivalent in Qlik Sense.

I have also tried several methods of manipulating the data to convert these to 0, but the issue is that these are not true null values, but rather missing values (type two, I believe!) so there is no data to convert. Does anyone have any suggestions on how to do this?

Thanks

3 Replies
Sandy1
New Contributor II

Re: How to convert null values to 0

I usually try the Alt() function to replace null or missing values by 0. Maybe you could give this a try if not done yet.
Syntax: Alt(expression, 0)
andrescc
New Contributor III

Re: How to convert null values to 0

Hi,

You could try to convert your null values with this

NullAsValue A,B;

Set NullValue = 'NULL';

LOAD A,B from x.csv;
Channa
Valued Contributor III

Re: How to convert null values to 0

try use

if ( isNull(Column),0,Column)

 

Channa