Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Greetings to all,
I have a field of "item" codes.
These codes are in a hierarchy such that a parent is - for example- 16000, then the "first level" branching items will be (16060,16100, 16200).
The next level is defined by adding "a dot" after the first level branch like 16060.1, 16060.1.1...etc)
I want to aggregate each group (bottom-up).
Therefore, I want to use a variable and use a condition (while, where, unless ...etc) to aggregate each branch till the top.
Below is a screen shot of part of the code column (MOM Cat Ref.)
Kind regards
AK@
Hi,
one solution could be to create a hierarchy of Cat. Ref. numbers like this:
table1:
LOAD Text(CatRef) as CatRef,
Item,
Ceil(Rand()*1000) as Value
INLINE [
CatRef, Item
16000, Item1
16060, Item2
16061, Item3
16062, Item4
16062.1, Item5
16062.1.1, Item6
16062.1.2, Item7
16062.1.3, Item8
16062.1.4, Item9
16062.2, Item10
16062.2.1, Item11
16062.2.2, Item12
16062.2.3, Item13
16062.2.4, Item14
16062.2.5, Item15
16062.2.6, Item16
16062.2.6.1, Item17
16062.2.6.2, Item18
];
table2:
Hierarchy (CatRef,CatRefParent,CatRefLvl,,CatRefLvl,'CatRefHierarchy',,'CatRefDepth')
LOAD Distinct *,
CatRef as CatRefLvl;
LOAD Text(Pick(RangeMin(IterNo(),2),SubField(CatRef,'.',1),Left(CatRef,Index(CatRef&'.','.',IterNo())-1))) as CatRef,
Text(Pick(RangeMin(IterNo(),3),Floor(SubField(CatRef,'.',1),1000),SubField(CatRef,'.',1),Left(CatRef,Index(CatRef&'.','.',IterNo()-1)-1))) as CatRefParent
Resident table1
While IterNo() <= SubStringCount(CatRef,'.')+1;
hope this helps
regards
Marco
Hi,
one solution could be to create a hierarchy of Cat. Ref. numbers like this:
table1:
LOAD Text(CatRef) as CatRef,
Item,
Ceil(Rand()*1000) as Value
INLINE [
CatRef, Item
16000, Item1
16060, Item2
16061, Item3
16062, Item4
16062.1, Item5
16062.1.1, Item6
16062.1.2, Item7
16062.1.3, Item8
16062.1.4, Item9
16062.2, Item10
16062.2.1, Item11
16062.2.2, Item12
16062.2.3, Item13
16062.2.4, Item14
16062.2.5, Item15
16062.2.6, Item16
16062.2.6.1, Item17
16062.2.6.2, Item18
];
table2:
Hierarchy (CatRef,CatRefParent,CatRefLvl,,CatRefLvl,'CatRefHierarchy',,'CatRefDepth')
LOAD Distinct *,
CatRef as CatRefLvl;
LOAD Text(Pick(RangeMin(IterNo(),2),SubField(CatRef,'.',1),Left(CatRef,Index(CatRef&'.','.',IterNo())-1))) as CatRef,
Text(Pick(RangeMin(IterNo(),3),Floor(SubField(CatRef,'.',1),1000),SubField(CatRef,'.',1),Left(CatRef,Index(CatRef&'.','.',IterNo()-1)-1))) as CatRefParent
Resident table1
While IterNo() <= SubStringCount(CatRef,'.')+1;
hope this helps
regards
Marco
Many thanks........really appreciate your efforts
Kind regards
AK
you're welcome
regards
Marco