Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
kristof_j
Creator III
Creator III

Product tree without empty nodes

Hi

I'm using a product tree with many levels which aren't all filled in for all the products. Some products have all levels, others have only a few.

Something like this where only product 1 had all levels and the others don't.

kristof_j_0-1674553779528.png

If i make a pivot table it's messy. The products which don't have all  levels show empty values.

kristof_j_1-1674553844665.png

Is there a way to remove these empty values? With a calculated dimension?

In real life the product tree is a table linked to the sales. So the yellow marked values are null values.

Test data used for this example:

Load * Inline [
clients, product, level1, level2, level3
11, 1, a, aa, aaa
22, 2, b, bb,
33, 3, c,,
44, 4, a, AA
55, 5, a,
];

 

Labels (1)
4 Replies
vinieme12
Champion III
Champion III

create a hierarchy table

vinieme12_0-1674618924663.png

 

temp:
Load clients,product,if(len(trim(level1))<1,null(),level1) as level1 ,if(len(trim(level2))<1,null(),level2) as level2,if(len(trim(level3))<1,null(),level3) as level3 Inline [
clients,product,level1,level2,level3
11,1,a,aa,aaa
22,2,b,bb,
33,3,c,,
44,4,a,AA
55,5,a,
];

hierarchy:
CrossTable(levels,level, 1)
load clients,level1,level2,level3 
Resident temp;

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
kristof_j
Creator III
Creator III
Author

Thx but it needs to be the other way around. In your solution the product is the start of the tree but it should be the end. Otherwise you don't have an overview of the tree itself. In my example you can see that level1-a has 110 clients. In your example you don't have that kind of information.

The product tree is about 12.000 products long and 9 levels deep. But as in the example, some products end at level 9 and other at level 3 ... So the products that end at level 3 don't need to show the other empty levels.

 

vinieme12
Champion III
Champion III

just add product to the hierarchy table, but you still need product dimension from fact table to aggregate on in the pivot table

 

vinieme12_0-1674634154187.png

 

 

 

hierarchy:
CrossTable(levels,level, 1)
load product,level1,level2,level3 , product
Resident temp;

 

 

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
kristof_j
Creator III
Creator III
Author

I guess I don't understand your solution.

In your visualization you can't see the tree. You don't know how many clients are in product group 'a'. 

I can switch the dimensions so I can see how many clients have product group 'a' but then I don't know anymore that 'aa' falls under 'a'.

kristof_j_0-1674636623585.png

A drill down like a pivot table is going to be necessary as the product tree is very large.
Starting at a high level and drilling down to lower levels if necessary. Sometimes selection a product group and get an overview of all the underlying products (as in my first screenshot).

I managed to solves this in QlikView with an expression but not in Qlik Sense. 

I added a new field

temp:
Load clients,
product,
level_no,
if(len(trim(level1))<1,null(),level1) as level1 ,
if(len(trim(level2))<1,null(),level2) as level2,
if(len(trim(level3))<1,null(),level3) as level3
Inline [
clients,product,level1,level2,level3, level_no
11,1,a,aa,aaa,3
22,2,b,bb,,2
33,3,c,,,1
44,4,a,AA,,2
55,5,a,,,1
];

 

Supressing dimensions when values are null. 
And this expression: if( max(num#(level_no), '0') >= Dimensionality(), sum(clients))

The expressions isn't completely correct but with a count(distinct clientnumber) it should be fine.

kristof_j_1-1674637440221.png

If I do the same in QS I get this
kristof_j_2-1674637708837.png