Qlik Sense - Comparing field values by "Parsing" the digits composing this values

[#1]

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@

[MarcoWedel · Accepted solution]

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

[#3]

Many thanks........really appreciate your efforts

Kind regards

AK

[MarcoWedel]

you're welcome

regards

Marco