topic Re: Flag for null values in QlikView

Flag for null values

Anonymous — Fri, 22 Jan 2016 07:15:48 GMT

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

Re: Flag for null values

sunny_talwar — Fri, 22 Jan 2016 10:59:58 GMT

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

Re: Flag for null values

tamilarasu — Fri, 22 Jan 2016 11:00:12 GMT

Hi Gaonyadiwe,

Try,

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

Re: Flag for null values

tamilarasu — Fri, 22 Jan 2016 11:01:19 GMT

You are fast Sunny.

Re: Flag for null values

sunny_talwar — Fri, 22 Jan 2016 11:02:31 GMT

Hahahaha

Re: Flag for null values

PradeepReddy — Fri, 22 Jan 2016 11:25:53 GMT

try like this also..

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

Re: Flag for null values

sunny_talwar — Fri, 22 Jan 2016 11:27:36 GMT

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

Re: Flag for null values

Anonymous — Fri, 22 Jan 2016 11:29:29 GMT

may be like this as well?

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

Re: Flag for null values

sunny_talwar — Fri, 22 Jan 2016 11:35:22 GMT

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?

Re: Flag for null values

PradeepReddy — Fri, 22 Jan 2016 11:35:26 GMT

yes sunny...

Re: Flag for null values

Fri, 22 Jan 2016 11:38:04 GMT

Hi Sunny,

instead of going field wise null handling try to use null handling of All Loaded Field in single Query (it is possible to handle null of all fields by single Query)

try to use this :

Map_ProductId_Null:

MAPPING LOAD

NULL(),

'0'

Autogenerate 1;

Map ProductId using Map_ProductId_Null;

Map_Product_Null:

MAPPING LOAD

NULL(),

'UNKNOWN'

Autogenerate 1;

Map ProductCategory,ProductName using Map_Product_Null;

Test1:

LOAD ProductId,

ProductCategory,

ProductName

FROM

D:\rnd\Test123.xlsx

(ooxml, embedded labels, table is Sheet1);

Regards

ajay Kushwaha

Re: Flag for null values

Mahamed_Qlik — Fri, 22 Jan 2016 11:39:57 GMT

Hi Gaonyadiwe,

You can try this too:

if(IsNull([Purchase Order Number]),1,0) as NullFlag

-Mahamed

Re: Flag for null values

Anonymous — Fri, 22 Jan 2016 11:41:27 GMT

I think it should work, I tried in past and it was working well

I was just sharing alternate or workaround not having any intention to make it complicate

Re: Flag for null values

Mahamed_Qlik — Fri, 22 Jan 2016 11:42:43 GMT

Or you can try this:

NullAsValue [Purchase Order Number];

Set NullValue = '1' ;

Then Load your data...

Regards,

Mahamed

Re: Flag for null values

Anonymous — Fri, 22 Jan 2016 11:53:05 GMT

Thanks a lot Sunny T, it works perfect