Calculation with empty/NULL fields

[Anonymous]

Hi,

I'm currently working on a Project, where I'm loading data from two different tables. One is called Storage and the other one Need. My Goal is to write a column in the loadskript where the Difference shows up. It should look like this:

Need         Storage           Diff
20                 30                     -10
5                    2                         3
10                  0                        10

It should look like this.

My problem is, that if a certain material is not stored at all, ergo 0, it gets deleted out of the table. I know that I cant calculate with NULL values, so I tried this:

LOAD NEED - IF(STORAGE,STORAGE,0) as Difference

and this

LOAD NEED - IF(ISNULL(STORAGE),0,STORAGE) as Difference

My result for both looks like this:

Need         Storage           Diff
20                 30                     -10
5                    2                         3
10                  -                          -

pls help :'(

[juraj_misina · Accepted solution]

Hi,
you can use Alt() function, which is a Qlik version of IsNull() (sort of):

LOAD
NEED-Alt(STORAGE, 0) as Difference

But your If condition looks valid to me, so possibly you're not dealing with null values but empty strings. Instead of checking for nulls you can check value lenght

LOAD NEED - IF(Len(STORAGE)=0,0,STORAGE) as Difference

Hope this helps

Juraj

[sunny_talwar]

Is this something you can do on the front end? If you can, I would just perform this using RangeSum() function as it ignore nulls

RangeSum(NEED, -STORAGE)

[Anonymous]

Thank you a lot!

This is a wonderful Community!

The alt() function worked for me!