Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
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.
May be try
Alt(If(Match(Trim(Upper(Status)),'INACTIVE','TERMINATED','ON HOLD','ON LEAVE','RESIGNED'),0,1),0)
it has the same outcome, the nulls are still there
May be tryIf(Match(Trim(KeepChar(Upper(Status),'ABCDEFGHIJKLMNOPQRSTUVWXYZ- ')),'INACTIVE','TERMINATED','ON HOLD','ON LEAVE','RESIGNED',Null()),0,1)
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
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:
The untransformed field has nulls but for some reason it is showing up when trimmed and uppered?
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.?
Terminated_Flag is still working, other fields are trimmed and uppered but no expressions.
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