Creating hierarchical structure

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a table (mt) which is exemplified by the inline load script at the bottom. The table consist of MI (manufactured item) and LPN (local part number), the real data also consist of a few more dimensions. Some of the LPN are shared with the MI column, this is because they are part of a manufactured item sub-assembly. I need to sort this table into a hierarchical structure with sub-assembly levels. Anyone got any smart ideas how to do this? The number of sub-assemblies can vary greatly in the real data so I would prefer a solution that takes that into account.

What expect to get after sorting:

MI	SA1	SA2	LPN
AA			11
AA			12
AA			13
AA			14
AA			15
AA	AB		21
AA	AB		22
AA	AB		23
AA	AB		24
AA	AB		25
AC			31
AC			32
AC			33
AC			34
BB			41
BB			42
BB			43
BB			44
BB			45
BB	BC		51
BB	BC		52
BB	BC		53
BB	BD		61
BB	BD		62
BB	BD		63
BB	BD		64
BB	BD	BE	71
BB	BD	BE	72

What I start with:

mt:

LOAD * INLINE [

MI, LPN

AA, 11

AA, 12

AA, 13

AA, 14

AA, 15

AA, AB

AB, 21

AB, 22

AB, 23

AB, 24

AB, 25

AC, 31

AC, 32

AC, 33

AC, 34

BB, 41

BB, 42

BB, 43

BB, 44

BB, 45

BB, BC

BB, BD

BC, 51

BC, 52

BC, 53

BD, 61

BD, 62

BD, 63

BD, 64

BD, BE

BE, 71

BE, 72

];

Thanks in advance!

Finn

Re: Creating hierarchical structure

Gysbert_Wassenaar — Fri, 20 May 2016 09:18:11 GMT

See these blog posts:

Unbalanced, n-level hierarchies

Bill of Materials

Re: Creating hierarchical structure

Fri, 20 May 2016 10:17:07 GMT

Thank you for the reply, unfortunately I can not find an answer to my question in those blog posts.

My data is missing the level info that those posts are based on, the level calculation is basically what I'm trying to achieve.

MI	SA1	SA2	LPN
AA			11
AA			12
AA			13
AA			14
AA			15
AA	AB		21
AA	AB		22
AA	AB		23
AA	AB		24
AA	AB		25
AC			31
AC			32
AC			33
AC			34
BB			41
BB			42
BB			43
BB			44
BB			45
BB	BC		51
BB	BC		52
BB	BC		53
BB	BD		61
BB	BD		62
BB	BD		63
BB	BD		64
BB	BD	BE	71
BB	BD	BE	72

MI	SA1	SA2	LPN
AA			11
AA			12
AA			13
AA			14
AA			15
AA	AB		21
AA	AB		22
AA	AB		23
AA	AB		24
AA	AB		25
AC			31
AC			32
AC			33
AC			34
BB			41
BB			42
BB			43
BB			44
BB			45
BB	BC		51
BB	BC		52
BB	BC		53
BB	BD		61
BB	BD		62
BB	BD		63
BB	BD		64
BB	BD	BE	71
BB	BD	BE	72

topic Re: Creating hierarchical structure in QlikView

Creating hierarchical structure

Re: Creating hierarchical structure

Re: Creating hierarchical structure

MI	SA1	SA2	LPN
AA			11
AA			12
AA			13
AA			14
AA			15
AA	AB		21
AA	AB		22
AA	AB		23
AA	AB		24
AA	AB		25
AC			31
AC			32
AC			33
AC			34
BB			41
BB			42
BB			43
BB			44
BB			45
BB	BC		51
BB	BC		52
BB	BC		53
BB	BD		61
BB	BD		62
BB	BD		63
BB	BD		64
BB	BD	BE	71
BB	BD	BE	72