Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have some raw data that looks like this:
Product |
Field type | Status | Value |
A | Status | Done | Null |
A | Value 1 | Null | 10 |
A | Value 2 | Null | 5 |
B | Status | Production | Null |
B | Value 1 | Null | 15 |
B | Value 2 | Null | 10 |
I want to create a table visualization in Qlik Sense that looks like this
Product | Status | Value 1 | Value 2 |
A | Done | 10 | 5 |
B | Production | 15 | 10 |
Problem: I can get a table that looks like this
Product | Status | Value 1 | Value 2 |
A | Null | 10 | 5 |
A | Done | Null | Null |
B | Null | 15 | 10 |
B | Production | Null | Null |
The rows for the products either gets Status or Values, otherwise Null
When I try to do set analysis...
Sum(TOTAL <[Product]> [Value 1] ) and
Sum(TOTAL <[Product]> [Value 2] )
I can get Values on all rows. However, when I go to the table dimension settings for Status in and hide it's null cells, all the values also disappear.
How can this be solved?
Kind regards
Hi, you can do a joins to have all data in one row like:
OrigData:
<LoadYourTable>;
TransformData:
Noconcatenate LOAD Product, Status Resident OrigData Where [Field type]='Status';
Left Join (TransformData)
LOAD Product, Value as [Value 1] Resident OrigData Where [Field type]='Value 1';
Left Join (TransformData)
LOAD Product, Value as [Value 2] Resident OrigData Where [Field type]='Value 2';
DROP Table OrigData;
Another option could be adding status as a measure, like:
MinString({<Status={'Done','Production'}>} Status)
Hi, you can do a joins to have all data in one row like:
OrigData:
<LoadYourTable>;
TransformData:
Noconcatenate LOAD Product, Status Resident OrigData Where [Field type]='Status';
Left Join (TransformData)
LOAD Product, Value as [Value 1] Resident OrigData Where [Field type]='Value 1';
Left Join (TransformData)
LOAD Product, Value as [Value 2] Resident OrigData Where [Field type]='Value 2';
DROP Table OrigData;
Another option could be adding status as a measure, like:
MinString({<Status={'Done','Production'}>} Status)
1) Option 1 :Front End only solution
Dimension
Product
Status
Measure
Value 1
=SUM( aggr(nodistinct Sum({<Fieldtype={'Value 1'}>} Value),Product))
Value 2
=SUM( aggr(nodistinct Sum({<Fieldtype={'Value 2'}>} Value),Product))
2)Option 2 : Script solution (Recommended to fix data issues)
temp:
Load * from YourDataSource;
NOCONCATENATE
Main:
Load
Product
,Fieldtype
,if(Product=Peek(Product),Peek(new_Status),Status) as new_Status
,if(Value=Peek(Value),Peek(new_Value),Value) as new_Value
Resident temp
Order by Product ,Status DESC;
Then in charts use
Dimensions
Product, new_Status
Measures
Value1
sum({<Fieldtype={'Value 1'}>}new_Value)
Value2
sum({<Fieldtype={'Value 2'}>}new_Value)
Thank you both for good ideas! I used the method of left joining a 'status' table with each 'value' separately as you suggested Rubenmarin.