topic IsNull() widespread mistake, or genuine function? in QlikView

IsNull() widespread mistake, or genuine function?

atsushi_saijo — Wed, 12 Oct 2016 07:57:01 GMT

Hi Gurus,

I have been refactoring codes, and some developer use IsNull() in such manner:

*To code "create a flag field, if DocType is null, then 1 otherwise 0"

If(IsNull(DocType), 1, 0) as flg_DocTypeIsNull, ....

In my opinion, this is incorrect and we should code:

If(IsNull(DocType)=-1, 1, 0) as flg_DocTypeIsNull, ....

Am I right, or is IsNull() really function, giving true or false? As long as I see the log, the former is resulting nothing.

I would apprecaite for your wisdom.

Re: IsNull() widespread mistake, or genuine function?

vardhancse — Wed, 12 Oct 2016 08:33:47 GMT

Correct If(IsNull(DocType)=-1, 1, 0) will give -ve values instead +ve values

Re: IsNull() widespread mistake, or genuine function?

sunny_talwar — Wed, 12 Oct 2016 08:53:57 GMT

IsNull() does give True and false. If you try this without if statement IsNull(DocType), you will see -1 (for true) and 0 (for false). So when -1, assign a value 1, and when 0, assign a value 0 is done by this

If(IsNull(DocType), 1, 0) as flg_DocTypeIsNull

Re: IsNull() widespread mistake, or genuine function?

Peter_Cammaert — Wed, 12 Oct 2016 08:55:51 GMT

No this is perfectly correct. Every expression somehow results in a (numerical) value. If evaluated in a logical context (using relational operators), true() is equal to numerical value -1, and false() is equal to 0.

Now QlikView Load Script borrows some techniques from C-based languages in that you do not have to specify some expressions in an explicit way all the time. Some parts - if not present - are assumed. For example, these are all valid (identical) logical expressions:

IF (IsNull(DocType) = true(), 1, 0) // Explicit version. Too many characters?

IF (IsNull(DocType) = -1, 1, 0) // IsNull will return -1 (= true) or 0 (= false)

IF (IsNull(DocType), 1, 0) // IsNull(DocType) = true() is assumed

Actually, true will be more like "non-zero", so that the following will also work (although your intuition may complain):

IF (1, 'true', 'false') // returns true

IF (0, 'true', 'false') // returns false

IF (A+5, 1, 0) // depends on the result being 0 or non-zero

Of course, this also means that the following messes with everything said.

IF (1 = true(), 'true', 'false') // What do you get?

The start of the expression forces eveything to go numerical, and 1 is not equal to -1.

Best,

Peter