topic Re: Product tree without empty nodes in App Development

Product tree without empty nodes

kristof_j — Tue, 24 Jan 2023 09:55:20 GMT

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

Re: Product tree without empty nodes

vinieme12 — Wed, 25 Jan 2023 03:55:52 GMT

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;

Re: Product tree without empty nodes

kristof_j — Wed, 25 Jan 2023 07:39:50 GMT

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.

Re: Product tree without empty nodes

vinieme12 — Wed, 25 Jan 2023 08:09:23 GMT

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;

Re: Product tree without empty nodes

kristof_j — Wed, 25 Jan 2023 09:08:49 GMT

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