Having problem with NULL in set analysis

[Anonymous]

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

[MK_QSL]

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)

[Anonymous]

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

[MK_QSL]

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)

[Anonymous]

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

[nizamsha]

Hi

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)

[gardenierbi · Accepted solution]

Try this :