topic Re: Calculation with empty/NULL fields in QlikView

Calculation with empty/NULL fields

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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 :'(

Re: Calculation with empty/NULL fields

juraj_misina — Thu, 15 Nov 2018 10:50:07 GMT

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

Re: Calculation with empty/NULL fields

sunny_talwar — Thu, 15 Nov 2018 12:11:47 GMT

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)

Re: Calculation with empty/NULL fields

Anonymous — Thu, 15 Nov 2018 12:25:07 GMT

Thank you a lot!

This is a wonderful Community!

The alt() function worked for me!