Creating new Fields from existing ones.

[gitguto]

Hello everyone,

I have a table like this:

Material	Component	level	position	qty
Car	Car	0	1	1
Car	Engine	0.1	1	1
Car	Seats	0.1	2	4
Car	Structure	0.1	3	1
Car	Piston	0.1.1	1	1
Car	Alternator	0.1.1	2	1
Car	Valves	0.1.1	3	8
Car	Seat Structure	0.1.2	1	1
Car	Cushion	0.1.2	2	500
Car	Cottom	0.1.2.2	1	400
Car	Plastic	0.1.2.2	2	100

Explanation: This table shows all the parts I need to make 1 car and the components that I need to make these parts. And further, the material I need to make these components.

To make 1 car, I need: 1 Engine, 4 Seats and 1 Structure. 

To make 1 Engine, I need: 1 Piston,  1 Alternator, and 8 valves.

To make 1 Seat, I need: 1 Seat Structure and 500g of Cushion.

To make 1 Cushion, I need: 400g of Cottom and 100g of Plastic.

P.S. I left the table 'incomplete' to better reflect my real data. I have different level steps for different Materials, in this case, the further we go is 4 levels down: Car - Seat - Cushion - Cottom/Plastic. But mind that for other materials I can go up to 14 levels down with multiple steps down the way.

The "level" field is a pathway to understanding at which level we are, and to understanding what material is composed by that component. 

What I need to do is to be able to navigate through this levels, in a drop-down dimension. I need a bar chart in which the user can select "Car" among other materials and the chart would filter down to the first level, that is: Engine, Seats, Structure. Than the user could select one of the parts, if the user select Seat, it would filter down to Cushion and Seat Structure, and so on. The Measure would be the qty field.

My problem is that I can't use the same field in multiple levels of the drop-down master dimension, as any filter applied to it would be referencing itself.

Any solution for this problem would be appreciated it, either on the load script, maybe exploding each level in a new field, or by set analysis. My dataset is very large, so maybe a set analysis approach would be more efficient.

I've tried using the len(level) as the dropdown rule, but couldnt get the result I need.

[Digvijay_Singh]

In your data how do you identify that Piston(0.1.1) is a child of Engine(0.1) and not a child of  Seats(0.1) because both Engine and Seat has got level 0.1?

[gitguto]

The level field is composed by the last level&position

So the last '1'  refers to the position inside the level 0.1.

So:
0 = Car (only item in the level, position = 1)

0.1 = refers to all materials that are part of the level 0, position 1

0.1.1 = refers to all materials that are part of level 0.1, position 1.

Piston(0.1.1)

Component	Level	Position
Engine	0.1	1
Seats	0.1	2
Structure	0.1	3

[gitguto]

I was able to create new columns with the levels with something like this in the load script:

if(len(level) = 1, matnr_act, '') as First,
if(len(level) = 3, matnr_act, '') as Second,
if(len(level) = 5, matnr_act, '') as Third,
if(len(level) = 7, matnr_act, '') as Forth

This generated the following table, but WITHOUT THE VALUES IN RED. Because the script is not able to find the relationship between level and position.

Material	Component	level	position	qty	len(level)	First	Second	Third	Forth
Car	Car	0	1	1	1	Car
Car	Engine	0.1	1	1	3	Car	Engine
Car	Seats	0.1	2	4	3	Car	Seats
Car	Structure	0.1	3	1	3	Car	Structure
Car	Piston	0.1.1	1	1	5	Car	Engine	Piston
Car	Alternator	0.1.1	2	1	5	Car	Engine	Alternator
Car	Valves	0.1.1	3	8	5	Car	Engine	Valves
Car	Seat Structure	0.1.2	1	1	5	Car	Seats	Seat Structure
Car	Cushion	0.1.2	2	500	5	Car	Seats	Cushion
Car	Cottom	0.1.2.2	1	400	7	Car	Seats	Cushion	Cottom
Car	Plastic	0.1.2.2	2	100	7	Car	Seats	Cushion	Plastic

If I could complete the table with the values in red, this would solve my problem.

[oskartoivonen · Accepted solution]

This appears to be a typical hierarchy scenario. You need to transform your data into child-parent relationships and run it through a Hierarchy-LOAD.

Example data:

Component	ChildID	ParentID
Car	1
Engine	11	1
Seats	12	1
Structure	13	1
Piston	111	11
Alternator	112	11
Valves	113	11
Seat Structure	121	12
Cushion	122	12
Cottom	1221	122
Plastic	1222	122

[gitguto]

Thank you very much! Hierarchy load was exactly what I needed. I didn't know it existed.

https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/DataSource/hierarchies.ht...