Product tree without empty nodes

[kristof_j]

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.

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

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,
];

[vinieme12]

create a hierarchy table

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;

[kristof_j]

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]

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

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

[kristof_j]

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'.

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.

If I do the same in QS I get this