Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

etrotter
Contributor II

Wierd Nulls Popping up when Creating Flags

Good Afternoon,

I am experiencing issues when creating flags where many of them are nulls for no apparent reason:

In the Script:

    Trim(Upper(Status)) as Status,

    If(Trim(Upper(Status))='INACTIVE',0,If(Trim(Upper(Status))='TERMINATED',0,If(Trim(Upper(Status))='ON HOLD',0,If(Trim(Upper(Status))='ON LEAVE',0,If(Trim(Upper(Status))='RESIGNED',0,1))))) as Active_Flag,

    if(Trim(Upper(Status))='TERMINATED',1,0) as Terminated_Flag

Output:

Capture.PNG

As you can see, the for the inactive it is null in one place but is working correctly in another. The if statement is basically the same for the terminated flag, but it is coming through fully with no nulls.

Please let me know if you have any ideas!

Thanks,

Erica

23 Replies
sasiparupudi1
Honored Contributor III

Re: Wierd Nulls Popping up when Creating Flags

Try

If(Match(Trim(Upper(Status)),'INACTIVE','TERMINATED','ON HOLD','ON LEAVE','RESIGNED'),0,1) as Active_Flag,

etrotter
Contributor II

Re: Wierd Nulls Popping up when Creating Flags

Hi Sasidhar,

I tried your script, and it still has the nulls in the same places the original one has.

Thanks,

Erica

sasiparupudi1
Honored Contributor III

Re: Wierd Nulls Popping up when Creating Flags

Do you have any other Diemnsions in the table?  the status field is duplicated and it might be that you need aggregation here in the if condition

etrotter
Contributor II

Re: Wierd Nulls Popping up when Creating Flags

Yes, there are other dimensions such as address, workerID, and several others, but if they are causing null values, why is it not occurring with the terminated_flag when I built it the same way?

shilpan9166
Contributor II

Re: Wierd Nulls Popping up when Creating Flags

You can try:

NullAsValue fieldname1, fieldname2; (or even * for all fields)

Set NullValue = 'NULL';

prior to loading the table with field1, field2 etc.

shilpan9166
Contributor II

Re: Wierd Nulls Popping up when Creating Flags

Or in your case

set NillValue = '0';

etrotter
Contributor II

Re: Wierd Nulls Popping up when Creating Flags

unfortunately this is not the only case. There are other places in the script where it is supposed to be a 1 but the data is not coming through. I tried putting a text() around the field, but this did not work either.

MVP
MVP

Re: Wierd Nulls Popping up when Creating Flags

Erica,

could you add an expression to your table:

=Len(Status)

and repost the table screenshot?

edit:

And would it be possible that you add a small sample app?

juraj_misina
Valued Contributor

Re: Wierd Nulls Popping up when Creating Flags

Hi Erica,

any chance that field contains non-braking space? I learned (the hard way) that Trim() would not trim non-braking space. Try Trim(Replace(Status, chr(160), ''))

Hope this helps.

Juraj