topic Re: Excluding rows with null dimension values without excluding the measures on these rows (table visualization) in App Development

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

salcin — Fri, 20 May 2022 13:58:43 GMT

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

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

rubenmarin — Sat, 21 May 2022 07:57:44 GMT

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)

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

vinieme12 — Sat, 21 May 2022 14:38:50 GMT

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)

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

salcin — Mon, 13 Jun 2022 07:55:37 GMT

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