topic Re: Null Value Filter in QlikView

Null Value Filter

Anonymous — Wed, 06 Aug 2014 14:01:37 GMT

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

Re: Null Value Filter

MK_QSL — Wed, 06 Aug 2014 14:19:18 GMT

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;

Re: Null Value Filter

jonathandienst — Wed, 06 Aug 2014 14:23:12 GMT

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

Re: Null Value Filter

its_anandrjs — Wed, 06 Aug 2014 14:37:09 GMT

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

Re: Null Value Filter

Anonymous — Wed, 06 Aug 2014 14:38:32 GMT

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

Re: Null Value Filter

Anonymous — Wed, 06 Aug 2014 14:58:18 GMT

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

Re: Null Value Filter

jonathandienst — Thu, 07 Aug 2014 07:31:43 GMT

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

Re: Re: Null Value Filter

Anonymous — Thu, 07 Aug 2014 07:44:38 GMT

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

Re: Null Value Filter

jonathandienst — Thu, 07 Aug 2014 07:58:10 GMT

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

Re: Null Value Filter

Anonymous — Thu, 07 Aug 2014 08:02:54 GMT

Hi Jonathan,

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

Thanks & Regards

Jeba

Re: Null Value Filter

tobias_klett — Thu, 07 Aug 2014 08:03:35 GMT

Hi Jebamalai,

why don't you do another resident load and fill the null values like this:

if(isnull( [Hazard Types]) , 'null', [Hazard Types] ) as [Hazard Types],

Hope this helps
Tobias

Re: Null Value Filter

Anonymous — Thu, 07 Aug 2014 08:38:53 GMT

I have so many tables to do such resident