Flag for null values

[Anonymous]

Good Day guys,

Please assist, im trying to create a flag for null values on the Purchase Order Number field.

I created the flag but its bringing all values as 0 (Null and not null values). Now i cant filter on the null ones. Attached below is the Script and the results that i get after loading. Null and not null flag is the same. Thank you

[sunny_talwar · Accepted solution]

Try adding Trim to your Len() function. There might be white space in those rows:

If(Len(Trim([Purchase Order Number)) = 0, 1, 0) as NullFlag

Nulls are flagged 1 and non-nulls will be 0

[tamilarasu]

Hi Gaonyadiwe,

Try,

If(Len(Trim(Purchase Order Number))=0, 0,1) as NullFlage

[tamilarasu]

You are fast Sunny.

[sunny_talwar]

Hahahaha

[PradeepReddy]

try like this also..

if(isnull(Purchase Order Number),1,0)  as  Flag

[sunny_talwar]

As long as I know, this won't work pradeep because Len() function did find some white space, so its not null.

[Anonymous]

may be like this as well?

If([Purchase Order Number]=' ', 1,0) as NullFlage

[sunny_talwar]

But how will this capture true nulls?

May be this:

If([Purchase Order Number]=' ' or IsNull([Purchase Order Number]), 1,0) as NullFlage

But why make this so complicated, why not just Len(Trim()) test?

[PradeepReddy]

yes sunny...