IS not NULL statement is not working in resident load

[ananyaghosh]

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

[rahulpawarb]

As a workaround, you can add below condition:

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

Regards!

Rahul Pawar

[marcus_malinow]

Ok

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])

[tripatirao]

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

[swuehl · Accepted solution]

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.

[ananyaghosh]

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?

[swuehl]

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.