topic Re: IS not NULL statement is not working in resident load in QlikView

IS not NULL statement is not working in resident load

ananyaghosh — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am using the below code:

[Division]:

//first 100;

load

distinct

"Trn Key",

[Manager Wholesaler],

[Firm Channel Desc] & ' - ' & [Manager Name] as [Division Name],

[Firm Channel Desc] & ' - ' & [Manager Name] & '_' & [Manager Wholesaler] as Division_Manager_Wholeseller

resident [Temp Stagging Table]

where

[Firm Channel Desc], [Division Name] IS not NULL;

but I am getting the error when I am using IS NOT NULL statement.

So is there any work around exists, so that I can use NULL checking here and not loading the NULL values.

Thanks,

Sandip

Re: IS not NULL statement is not working in resident load

rahulpawarb — Tue, 08 Aug 2017 08:54:27 GMT

As a workaround, you can add below condition:

Where Len([Division Name]) > 0;
//or 
Where IsNull([Division Name]) = 0;

Regards!

Rahul Pawar

Re: IS not NULL statement is not working in resident load

marcus_malinow — Tue, 08 Aug 2017 08:55:12 GMT

not entirely clear - are you looking to compare [Firm Channel Desc] AND [Division Name]? If so, try this:

NOT IsNull([Firm Channel Desc]) AND NOT IsNull([Division Name])

Re: IS not NULL statement is not working in resident load

tripatirao — Tue, 08 Aug 2017 08:59:26 GMT

Dear

add

[Firm Channel Desc],

[Division Name],

and try once

[Division]:

//first 100;

load

distinct

"Trn Key",

[Firm Channel Desc],

[Division Name],

[Manager Wholesaler],

[Firm Channel Desc] & ' - ' & [Manager Name] as [Division Name1],

[Firm Channel Desc] & ' - ' & [Manager Name] & '_' & [Manager Wholesaler] as Division_Manager_Wholeseller

resident [Temp Stagging Table]

where

[Firm Channel Desc], [Division Name] IS not NULL;

Regards

Tripati

Re: IS not NULL statement is not working in resident load

swuehl — Tue, 08 Aug 2017 08:59:31 GMT

Also have a look at the HELP for the correct syntax of IsNull():

IsNull ‒ QlikView

...

Where

Not IsNull(FieldA) and Not IsNull(FieldB);

Or use

...

Where

Len(Trim(FieldA)) and Len(Trim(FieldB));

instead to handle NULL or empty spaces only field values.

Re: IS not NULL statement is not working in resident load

ananyaghosh — Tue, 08 Aug 2017 09:14:09 GMT

Can I use this as:

Where

Len(Trim(FieldA))>0 and Len(Trim(FieldB)) >0;

or if I use Len(Trim(FieldA)) and Len(Trim(FieldB)); - it will automatically handle the empty values in the field means does not load the rows from those fields that hold the empty values?

Re: IS not NULL statement is not working in resident load

swuehl — Tue, 08 Aug 2017 09:44:26 GMT

Both lines should be equivalent.

But you need to define if you want to remove all records where both field values are empty / NULL or where at least one of the field values is empty / NULL. Change the AND to OR then.