Nulls in the database, nulls in qlik sense

[noemibuscemi01]

Hi everyone,

My question today is:

I've got some NULL values in my database, when I perform a sum on qlik sense it returns zero, but I want to show NULL as in the database or blank. 

I know that qlik sense treats nulls as zero values, what I tried to do is forcing nulls/blanks with an if statement (if field=0, '', field) but doing this way all zeros turn blank, but I only want that 0 because of nulls in the db turns blank. How can I distinguish these values?

Please help.

[NitinK7]

You Can try  if(  LEN(field)=0,  'NULL',  filed)

[MayilVahanan]

Hi @noemibuscemi01 

Try like below

If(Len(Trim(Field))=0 or isnull(Field), '', Field)

[noemibuscemi01]

not working 😞

[noemibuscemi01]

not working 😞

[ArnadoSandoval]

Hi @noemibuscemi01  @MayilVahanan @NitinK7 

Qlik features these system variables and statement to handle NULLs:

NullInterpret ; NullValue  and NullAsValue you should read their help pages.

I attached a demo project illustrating their usage.

Hope this helps

[noemibuscemi01]

Thanks! I'll take a look 😊

[noemibuscemi01]

Hi ArnadoSandoval,

Your links were really useful to better understand the null topic in Qlik.

However, I couldn't find a solution to my issue. The problem is that I have some nulls in the database and I want them to remain nulls in qlik too when I perform a sum. I know sums return zero values, is there a way to force the null value instead?

Attached some screenshots:
1- the field as it appears without applying filters, and it's ok in this way
2- the expression used to calculate that field
3- after I apply filters on year and on another field (RagioneSociale)

The 0 you see after I apply filters is a NULL value in the database and I want it to be a NULL (or a blank if it's simpler) in qlik too.

[noemibuscemi01 · Accepted solution]

Found the solution!

=if (isnull(field1), '',
sum(field1)
)