Wierd Nulls Popping up when Creating Flags

[etrotter]

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

[sasiparupudi1]

Try

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

[etrotter]

Hi Sasidhar,

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

Thanks,

Erica

[sasiparupudi1]

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]

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?

[shilpan]

You can try:

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

Set NullValue = 'NULL';

prior to loading the table with field1, field2 etc.

[shilpan]

Or in your case

set NillValue = '0';

[etrotter]

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.

[swuehl]

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]

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