Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
Select 1 and Null in your listbox. Problem solved. If you want to do it in a set analysis expression use {<Type={'1','NULL'}>}
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
| |||||||||||||||||||||||||||||||||||||||||||
Sum({<Type-={'2'}>} Revenue) should do it if these are the only types in your data set.
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 |
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
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'}>}
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
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 |
Try
=Sum({<Type={1,"=Len(Type)=0"}>}Revenue)