Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
william_fu
Creator II
Creator 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
Employee
Employee

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

sunny_talwar

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

william_fu
Creator II
Creator II
Author

Flag is consistent with the measure:

flagprejuu.PNG

william_fu
Creator II
Creator II
Author

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
Employee
Employee

Hi William,

Can you share the Current SALDO formula?

Pedro

william_fu
Creator II
Creator II
Author

It's a very basic expression:

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

sunny_talwar

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
Employee
Employee

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

william_fu
Creator II
Creator II
Author

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