topic Re: Qlik Sense - Comparing field values by "Parsing" the digits composing this values in QlikView

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

Mon, 09 May 2016 17:05:52 GMT

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@

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

MarcoWedel — Tue, 10 May 2016 23:22:16 GMT

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

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

Wed, 11 May 2016 07:49:51 GMT

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

Kind regards

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

MarcoWedel — Wed, 11 May 2016 21:28:32 GMT

you're welcome

regards

Marco