How to show NULL in set analysis

[Anonymous]

Hi QlikView

I got one problem I have been trying to find solution to.

this is my dataset.

Sales Person	Type	Company	Revenue
Bill	1	Ferrari	100.000
John	1	Audi	250.000
David	1	Porche	300.000
Stuart	2	Fiat	450.000
Bond	2	Volvo	125.000
NULL	NULL	Tesla	130.000
NULL	NULL	VW	280.000
James	2	Ferrari	150.000
Barry	2	Audi	500.000

In my data set I have different Sales Person for company because the Sales Person can be different type

Sometimes I have NULL in the Sales Person and type.

What I am trying to solve is making a straight table that shows only information that has type 1 and Null (exclude 2)

I have been trying to use set analysis but I have problem regarding NULL.

I want the view to look like this

Sales Person	Type	Company	Revenue
Bill	1	Ferrari	100.000
John	1	Audi	250.000
David	1	Porche	300.000
NULL	NULL	Tesla	130.000
NULL	NULL	VW	280.000

[Gysbert_Wassenaar]

Select 1 and Null in your listbox. Problem solved. If you want to do it in a set analysis expression use {<Type={'1','NULL'}>}

[Anonymous]

Hi, Gysbert

one more question. If the dataset was like below.

Only thing I changed was the last two lines.

The lines are empty and not have NULL.

Then your set analysis does not work.

What can I do to make it work

Sales Person Type Company Revenue Bill 1 Ferrari    100.000     John 1 Audi    250.000     David 1 Porche    300.000     Stuart 2 Fiat    450.000     Bond 2 Volvo    125.000     James 2 Ferrari    150.000     Barry 2 Audi    500.000                                         Tesla 130.000      VW 280.000

[simenkg]

Sum({<Type-={'2'}>} Revenue) should do it if these are the only types in your data set.

[Anonymous]

hi, Simen

i get this table when I use your set analysis:

Sales Person	Type	Company	Revenue
Bill	1	Ferrari	100.000
John	1	Audi	250.000
David	1	Porche	300.000

But I want this set analysis:

Sales Person	Type	Company	Revenue
Bill	1	Ferrari	100.000
John	1	Audi	250.000
David	1	Porche	300.000
NULL	NULL	Tesla	130.000
NULL	NULL	VW	280.000

[#6 · Accepted solution]

Darri,

if you have true null values in your fields, you will struggle to show these in set analysis, as nulls are not selectable and set analysis is effectively a defined selection.

Try turning your nulls into real values, rather than true nulls and you should be able to use set analysis then

hope that helps

Joe

[simenkg]

Your best bet is to change the script to something like:

load

Sales,

if(isnull(Type) or len(Trim(Type))=0, 'NULL' , Type) as Type,

Company,

Revenue

from File.....;

then you can use {<Type={'1','NULL'}>}

[Gysbert_Wassenaar]

Replace the nulls in the script with real values. Nulls cannot be selected and are a pain to work with in set analysis expressions. See this document for more information: NULL handling in QlikView

[Anonymous]

Hi Try like this

Sales Person	Type	Company	Sum({<Type={'1','NULL'}>}Revenue)
			1060000
Bill	1	Ferrari	100000
David	1	Porche	300000
John	1	Audi	250000
NULL	NULL	Tesla	130000
NULL	NULL	VW	280000

[sasiparupudi1]

Try

=Sum({<Type={1,"=Len(Type)=0"}>}Revenue)