topic Re: Having problem with NULL in set analysis in QlikView

Having problem with 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. Pretty simple.

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

But I got one problem regarding making a straight table that shows only information that has type 1 and Null (exclude 3).

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

I want the view to look like this

Bill	Ferrari	100.000
John	Audi	250.000
David	Porche	300.000
NULL	Tesla	130.000
NULL	VW	280.000

Re: Having problem with NULL in set analysis

MK_QSL — Fri, 25 Sep 2015 14:59:06 GMT

Use below in your script

LOAD If(IsNull([Sales Person]) or Len(Trim([Sales Person]))=0, 'NULL',[Sales Person]) as [Sales Person],

If(IsNull(Type) or Len(Trim(Type))=0,'NULL',Type) as Type,

Company,

Revenue

FROM

[Dataset - SalesPerson.xlsx]

(ooxml, embedded labels, table is Sheet1);

Now create straight table

Dimension

[Sales Person]

Company

Expression

SUM({<Type = {'1','NULL'}>}Revenue)

Re: Having problem with NULL in set analysis

Anonymous — Fri, 25 Sep 2015 15:02:48 GMT

Do you have a solution that does not involve changing the script?

Re: Having problem with NULL in set analysis

MK_QSL — Fri, 25 Sep 2015 15:09:35 GMT

Straight Table

Dimension

=IF(Type = 1, [Sales Person], If(IsNull(Type) or Len(Trim(Type))=0,'NULL'))

=IF(Type = 1, Company, If(IsNull(Type) or Len(Trim(Type))=0,'NULL'))

For both above tick suppress when value is null

Expression

SUM(Revenue)

Re: Having problem with NULL in set analysis

Anonymous — Fri, 25 Sep 2015 15:24:07 GMT

ok, I like that answer.

One more question.

Can I select Sales Person that have type 1 in the list box. Lets say I select Bill and David but skip John.

Now I have select Bill and David. And I want my table to show there value and Null value like this:

Sales Person	Comapny	Revenue
Bill	Ferrari	100.000
David	Porche	300.000
NULL	Tesla	130.000
NULL	VW	280.000

Re: Having problem with NULL in set analysis

nizamsha — Sat, 26 Sep 2015 05:46:46 GMT

IF u Like it Mean u can try this one

Script

LOAD *,if(SalesPerson='NULL' or IsNull(SalesPerson)=-1,'Others',SalesPerson) as SalesPerson1,

if(Type='NULL' or IsNull(Type)=-1 ,'Others',Type) as Type1;

LOAD * Inline [

SalesPerson ,Type, Company, Revenue

Bill, 1 ,Ferrari, 100.000

John, 1, Audi , 250.000

David, 1 ,Porche , 300.000

Stuart, 3, Fiat , 450.000

Bond, 3, Volvo , 125.000

NULL, NULL ,Tesla , 130.000

NULL, NULL ,VW , 280.000

];

Dimension

=if(SalesPerson1='Others','Null',SalesPerson1)

Measure

=sum({$<Type1-={'3'}>}Revenue)

Re: Having problem with NULL in set analysis

gardenierbi — Sat, 26 Sep 2015 08:01:12 GMT

Try this :