Qlik Community

QlikView Extensions

Discussion Board for collaboration on QlikView Extensions.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

darrihilmarsson
Not applicable

Having problem with NULL in set analysis

Hi QlikView

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

this is my dataset. Pretty simple.

   

Sales PersonType CompanyRevenue
Bill1Ferrari   100.000    
John1Audi   250.000    
David1Porche   300.000    
Stuart3Fiat   450.000    
Bond3Volvo   125.000    
NULLNULLTesla   130.000    
NULLNULLVW   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

   

BillFerrari 100.000    
JohnAudi   250.000    
DavidPorche   300.000    
NULLTesla   130.000    
NULLVW

   280.000    

1 Solution

Accepted Solutions
gardenierbi
Not applicable

Re: Having problem with NULL in set analysis

Try this :

NotType3.png

6 Replies
MK_QSL
Not applicable

Re: Having problem with NULL in set analysis

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)

darrihilmarsson
Not applicable

Re: Having problem with NULL in set analysis

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

MK_QSL
Not applicable

Re: Having problem with NULL in set analysis

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)

darrihilmarsson
Not applicable

Re: Having problem with NULL in set analysis

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 PersonComapnyRevenue
BillFerrari100.000
DavidPorche300.000
NULLTesla130.000
NULLVW280.000
nizamsha
Not applicable

Re: Having problem with NULL in set analysis

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
Not applicable

Re: Having problem with NULL in set analysis

Try this :

NotType3.png