Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

jebamalai
Valued Contributor

Null Value Filter

Dear Experts,

I have a table with one field say Hazard Types which doesn't have any null field.

When I connect this with my data model with many other tables there may few unmatched values (which may not Hazardours goods)

When I try to plot the straight table with many other values few Hazard Types values are null which is actually correct.

I have a list box of "Hazard Types" in my layout,now user wants to add one extra value "null" in this list box which should filter all the associated null values in the straight table.

Is this possible?

Thanks & Regards

Jeba

11 Replies
MVP
MVP

Re: Null Value Filter

You can achieve by creating something below in script...

Hazard_Temp:

Load * Inline

[

  GOODS, Hazard Types

  A, YES

  B, YES

];

Sales:

Load * Inline

[

  GOODS, Sales

  A, 100

  B, 120

  C, 1000

];

Outer Join (Hazard_Temp) Load Distinct GOODS Resident Sales;

NoConcatenate

Customer:

Load GOODS, If(IsNull([Hazard Types]),'NO',[Hazard Types]) as [Hazard Types] Resident Hazard_Temp;

Drop Table Hazard_Temp;

MVP
MVP

Re: Null Value Filter

Hi

I would use NullAsValue to render the null as an empty string. Just add the line

NullAsValue [Hazard Types];

before the LOAD statement for the table containing the Hazard Types field. It will then appear in the list box.

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: Null Value Filter

If you have small sample data then provide that will be easy to understand.

jebamalai
Valued Contributor

Re: Null Value Filter

Hi Jonathan,

Thanks for the reply.

I tried but it seems some where I am missing the logic.

Could you pls confirm what mistake I am doing here

NullAsValue [Customer Segment];

Jeba:

LOAD [Customer Segment],

    
[Product Category],

    
[Product Sub-Category],

    
[Product Name],

    
[Product Container],

    
[Product Base Margin],

    
[Ship Date],

    
Week([Ship Date]) as Weekly,

    
Diff

FROM

jebamalai
Valued Contributor

Re: Null Value Filter

Hi Anand,

Its a very big data model and fetching the sample data will be a issue.

If you could check my previous message,I want a extra value in "Customer Segment" added to my list box which should filter the null values if I create any tables.

Kindly note that there will not be any null fields actually in the "Customer Segment",but when I connect many tables in my datamodel,some connections has subset ratio less than 100 %.For this cases my Customer Segment shows null value in the tables.But user want a field "Null" in the "Customer Segment" list box and he want to filter null values.

Thanks & Regards

Jeba

MVP
MVP

Re: Null Value Filter

The load script looks correct. Can you be more specific about the problem?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jebamalai
Valued Contributor

Re: Re: Null Value Filter

Hi Jonathan,

Attaching one sample data for your kind reference.

Kindly refer the order id 100 in sheet "Jonathan".

This doesn't have the Customer Segment because its not the matched "Order ID".

Now user wants an extra field in the "Customer Segment" list box to filter those unmatched values.

Kindly help me.

Thanks & Regards

Jeba

MVP
MVP

Re: Null Value Filter

Hi

Your script has an error on tab Main2 (the incomplete statement in lines 3-6). Are you sure that you reloaded successfully and saved after the reload?

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jebamalai
Valued Contributor

Re: Null Value Filter

Hi Jonathan,

Yes,it throwed an error,but i just cancelled that and loaded the data.

Thanks & Regards

Jeba