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

How to show NULL in set analysis

Hi QlikView

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

this is my dataset.

Sales PersonType CompanyRevenue
Bill1Ferrari   100.000    
John1Audi   250.000    
David1Porche   300.000    
Stuart2Fiat   450.000    
Bond2Volvo   125.000    
NULLNULLTesla   130.000    
NULLNULLVW   280.000    
James2Ferrari    150.000    
Barry2Audi   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 PersonType CompanyRevenue
Bill1Ferrari   100.000    
John1Audi   250.000    
David1Porche   300.000    
NULLNULLTesla   130.000    
NULLNULLVW   280.000    
Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: How to show NULL in set analysis

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

9 Replies
Gysbert_Wassenaar
Not applicable

Re: How to show NULL in set analysis

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


talk is cheap, supply exceeds demand
darrihilmarsson
Not applicable

Re: How to show NULL in set analysis

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 PersonType CompanyRevenue
Bill1Ferrari   100.000    
John1Audi   250.000    
David1Porche   300.000    
Stuart2Fiat   450.000    
Bond2Volvo   125.000    
James2Ferrari    150.000    
Barry2Audi   500.000    
                                    Tesla130.000  
   VW280.000 
bwisenosimenkg
Not applicable

Re: How to show NULL in set analysis

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

darrihilmarsson
Not applicable

Re: How to show NULL in set analysis

hi, Simen

i get this table when I use your set analysis:

Sales PersonTypeCompanyRevenue
Bill1Ferrari   100.000   
John1Audi   250.000   
David1Porche   300.000 

But I want this set analysis:

Sales PersonTypeCompanyRevenue
Bill1Ferrari   100.000   
John1Audi   250.000   
David1Porche   300.000   
NULLNULLTesla   130.000   
NULLNULLVW   280.000
Not applicable

Re: How to show NULL in set analysis

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

bwisenosimenkg
Not applicable

Re: How to show NULL in set analysis

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

Re: How to show NULL in set analysis

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


talk is cheap, supply exceeds demand
rgvavihs
Not applicable

Re: How to show NULL in set analysis

Hi Try like this

Sales Person Type Company Sum({<Type={'1','NULL'}>}Revenue)
1060000
Bill1Ferrari100000
David1Porche300000
John1Audi250000
NULLNULLTesla130000
NULLNULLVW280000
sasiparupudi1
Not applicable

Re: How to show NULL in set analysis

Try

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