Define hierarchy by Id

[#1]

Hi to eveyone!

Please help!

I have a table:

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

....

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!

[#2]

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

[#3]

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.

[#4 · Accepted solution]

I think I have found a solution:

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

[MarcoWedel]

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

[#6]

Thank you very much, Marco!

[MarcoWedel]

You're welcome

regards

Marco