Excluding rows with null dimension values without excluding the measures on these rows (table visualization)

[salcin]

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

[rubenmarin · Accepted solution]

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)

[vinieme12]

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)

[salcin]

Thank you both for good ideas! I used the method of left joining a 'status' table with each 'value' separately as you suggested Rubenmarin.