Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
salcin
Partner - Contributor II
Partner - Contributor II

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

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

Labels (1)
1 Solution

Accepted Solutions
rubenmarin

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)

View solution in original post

3 Replies
rubenmarin

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
Champion III
Champion III

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)

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
salcin
Partner - Contributor II
Partner - Contributor II
Author

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