topic Re: How to show NULL in set analysis in QlikView

How to show NULL in set analysis

Anonymous — Mon, 26 Jan 2026 16:26:21 GMT

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

Re: How to show NULL in set analysis

Gysbert_Wassenaar — Tue, 29 Sep 2015 08:33:45 GMT

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

Re: How to show NULL in set analysis

Anonymous — Tue, 29 Sep 2015 08:47:51 GMT

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

Re: How to show NULL in set analysis

simenkg — Tue, 29 Sep 2015 08:48:09 GMT

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

Re: How to show NULL in set analysis

Anonymous — Tue, 29 Sep 2015 09:04:52 GMT

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

Re: How to show NULL in set analysis

Tue, 29 Sep 2015 09:08:41 GMT

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

Re: How to show NULL in set analysis

simenkg — Tue, 29 Sep 2015 09:11:28 GMT

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'}>}

Re: How to show NULL in set analysis

Gysbert_Wassenaar — Tue, 29 Sep 2015 09:12:32 GMT

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

Re: How to show NULL in set analysis

Anonymous — Tue, 29 Sep 2015 09:20:48 GMT

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

Re: How to show NULL in set analysis

sasiparupudi1 — Tue, 29 Sep 2015 09:30:54 GMT

Try

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