topic Re: Define hierarchy by Id in QlikView

Define hierarchy by Id

Wed, 28 Oct 2015 03:58:19 GMT

Hi to eveyone!

Please help!

I have a table:

1.1

1.1.1

1.1.2

1.1.2.1

1.1.2.2

1.1.2.2.1

1.2

1.2.1

....

How can I define that "1" is a parent for "1.1", "1.1" is a parent for "1.1.1" and "1.1.2". There is 4 levels of hierarchy.

I tried to use:

if(len(ID)>=len(previous(ID)),alt(peek(Parent),previous(ID))) as Parent

but it works only partly, it compares only previous and current row. But it should take ID when it find the first short (in length) ID from the all previous rows.

Thanks!

Re: Define hierarchy by Id

Wed, 28 Oct 2015 14:23:29 GMT

You can try with Hierarchy function in scripting

or use below expression:

Trim(Replace(ID,subfield(ID,'.',-1),'')) as ParentID

If you want put the null if there in no parent then modified the expression:

IF( ID = Trim(Replace(ID,subfield(ID,'.',-1),'')) , Null() , Trim(Replace(ID,subfield(ID,'.',-1),'')) ) AS ParentID

Re: Define hierarchy by Id

Wed, 28 Oct 2015 18:24:08 GMT

Thank you very much.

But it also works partially.

For example:

"1.1.6.1" - "..6." - wrong

"1.1.6.2" - "1.1.6" - ok

"1.1.6.6" - "1.1.." - wrong

"1.1.7.1" - "..7." - wrong

"1.1.7.7" - "1.1.." - wrong

So, as you see, in some cases it do not work. It is strange, because logically it should work correctly.

Re: Define hierarchy by Id

Wed, 28 Oct 2015 18:36:25 GMT

I think I have found a solution:

left(ID,index(ID,'.',-1)-1) as ParentID

Re: Define hierarchy by Id

MarcoWedel — Wed, 28 Oct 2015 23:45:41 GMT

Hi,

to get different selection and analysis options using the different hierarchy levels of your ID field, you could try something like:

tabIDs:
LOAD Text(ID) as ID,
    Text(Left(ID,Index(ID&'.','.',IterNo())-1)) as HierarchyID,
    IterNo() as Level
While IterNo()<=SubStringCount(ID,'.')+1;
LOAD * Inline [
    ID
    1
    1.1
    1.1.1
    1.1.2
    1.1.2.1
    1.1.2.2
    1.1.2.2.1
    1.2
    1.2.1
];

tabLevels:
Generic LOAD
  ID,
  'Level'&Level&'ID',
  HierarchyID
Resident tabIDs;

tabParents:
Generic LOAD
  ID,
  'Parent'&(SubStringCount(ID,'.')+1-Level)&'ID',
  HierarchyID
Resident tabIDs
Where Level<=SubStringCount(ID,'.');

hope this helps

regards

Marco

Re: Define hierarchy by Id

Thu, 29 Oct 2015 02:57:51 GMT

Thank you very much, Marco!

Re: Define hierarchy by Id

MarcoWedel — Thu, 29 Oct 2015 23:23:56 GMT

You're welcome

regards

Marco