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: 
etrotter
Creator II
Creator 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
etrotter
Creator II
Creator II
Author

Capture.PNG

etrotter
Creator II
Creator II
Author

Hi Juraj,

I tried it in the script but the nulls are still showing up:

    Trim(Upper(Status)) as Status,

    len(Status) as Length,

    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(Match(Upper(Trim(Replace(Status, chr(160), ''))),'INACTIVE','TERMINATED','ON HOLD','ON LEAVE','RESIGNED'),0,1) as Test_Flag,

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

The result is the screenshot above.

sasiparupudi1
Master III
Master III

May be try

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

etrotter
Creator II
Creator II
Author

it has the same outcome, the nulls are still there

sasiparupudi1
Master III
Master III

May be tryIf(Match(Trim(KeepChar(Upper(Status),'ABCDEFGHIJKLMNOPQRSTUVWXYZ- ')),'INACTIVE','TERMINATED','ON HOLD','ON LEAVE','RESIGNED',Null()),0,1)

swuehl
MVP
MVP

Hi Erica,

this looks kind of strange.

Status is a dimension and Length the expression I suggested?

Any chance you can upload a small sample (you can probably remove all confidential data, just create a minimal data model that still demonstrates your issue)?

If this is not possible, you need to describe your model and chart context (all dimensions and expressions used).

Regards,

Stefan

etrotter
Creator II
Creator II
Author

I tried that, but it still had nulls, so I decided to load in status without anything to see what it looked like as the field leave_Status:

       Status as leave_Status,

     Trim(Upper(Status)) as Status,

    len(Status) as Length,

    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(Match(Trim(KeepChar(Upper(Status),'ABCDEFGHIJKLMNOPQRSTUVWXYZ- ')),'INACTIVE','TERMINATED','ON HOLD','ON LEAVE','RESIGNED',Null()),0,1) as Test_Flag,

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

This was the outcome:

Capture.PNG

The untransformed field has nulls but for some reason it is showing up when trimmed and uppered?

sasiparupudi1
Master III
Master III

‌hmmm this is strange..pl comment the terminates_flag and see if it still loads status value..?

do you have any other expressions in the table.?

etrotter
Creator II
Creator II
Author

Terminated_Flag is still working, other fields are trimmed and uppered but no expressions.

juraj_misina
Luminary Alumni
Luminary Alumni

Hi Erica,

this really is strange. Do you load any other tables to your data model? Could this be a result of some association? If so, try loading only this one table and see what happens. I don' see how

Trim(Upper(Status)) as Status

can result in "INACTIVE" (or any other valid value) if the input value is Null().

Juraj