Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

williamfuu
Contributor II

"If and" statement with null handling in script

I have a flag which should trigger only when DtPrejuizo < DtReferencia, but not for rows where DtPrejuizo is null:

    If(Date(Date#([DtPrejuizo], 'YYYYMMDD')) < Date(Date#([DtReferencia], 'YYYYMMDD'))

    and Len(Trim([DtPrejuizo]))>0 , 1, 0) as FlagPrejuizo,

I'm using FlagPrejuizo={'0'} in the "Current SALDO" measure; it is working as expected for the first four rows, since DtPrejuizo > DtReferencia.

However the result is zero for the last two rows, where DtPrejuizo is null, when I want it to be the same as "SOMA SALDO"

_dterefeffe.PNG

I also tried a nested if, with the same results.

13 Replies
pedrobergo
New Contributor III

Re: "If and" statement with null handling in script

Hi Willian,

I recommend you to use alt() function. It works like that...

  • if the first expression returns a valid value, its use this value.
  • If the first expression returns a not valid value, like a null, its use the value of the second expression.

So you can do the following:

if( alt(dtprejuizo, today()) < dtreferencia, 1, 0) as flag

Pedro

MVP
MVP

Re: "If and" statement with null handling in script

May be add FlagPrejuizo to this table and see if the last two rows are flagged 0, 1 or are they null?

williamfuu
Contributor II

Re: "If and" statement with null handling in script

Flag is consistent with the measure:

flagprejuu.PNG

williamfuu
Contributor II

Re: "If and" statement with null handling in script

Pedro,

I tried your suggestion, but while the Alt() expression seems to be working, the flag still returns 1 for some reasonalt_today.PNG

pedrobergo
New Contributor III

Re: "If and" statement with null handling in script

Hi William,

Can you share the Current SALDO formula?

Pedro

williamfuu
Contributor II

Re: "If and" statement with null handling in script

It's a very basic expression:

Sum({$<FlagPrejuizo={0}>} Saldo)

MVP
MVP

Re: "If and" statement with null handling in script

What if you approach the other way...

If(If(Len(Trim(Date(Date#([DtPrejuizo], 'YYYYMMDD')))) = 0, Today(), Date#([DtPrejuizo], 'YYYYMMDD')) >= Date#([DtReferencia], 'YYYYMMDD'), 0, 1) as FlagPrejuizo,

pedrobergo
New Contributor III

Re: "If and" statement with null handling in script

I think that i understand you now. You need to set 0 to flag when dtprejuizo < dtreferencia OR dtprejuizo is null.

Try  this:

if( alt(dtprejuizo, (dtreferencia+1)) < dtreferencia, 1, 0) as flag

So, its compare dtreferencia´s next day with dtreferencia when dtprejuizo is null, then it wills return 0

Pedro

williamfuu
Contributor II

Re: "If and" statement with null handling in script

Same thing unfortunately.

I tested the null statements by themselves in the script and they seem to be the problem:

Len(DtPrejuizo) as NumChar,

IsNull(DtPrejuizo) as NuloPrej,

However adding the measure in the table works as intended, as you can see by the columns in yellow

wtf.PNG