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. 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 |
Try this :
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)
Do you have a solution that does not involve changing the script?
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)
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 |
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)
Try this :