Creating a single Hierarchy

[colinodonnel]

Hello there,

Is it possible to create a single tree out of 3 other 3 trees?

This is from excel

Area	Amount	Formula
A	1
B	1
C	2	C = A+B
D	1
E	1
F	2	F = D+E
G	4	G=A+B+C+D

Technically,  C F and G are top of the Tree.

However G should really be the single top of the tree as G is also G= C +F

Currently it creates this adjacent nodes table

Node	Parent
A	C
B	C
D	F
E	F
A	G
B	G
D	G
E	G

How can i convert / rationalise it to this?

AdjacentNodes
Node	Parent
A	C
B	C
D	F
E	F
C	G
F	G

Thank you

[ali_hijazi]

Hello
the below script should lead to what you want

source:
load * inline [
Area, Amount, Formula
A, 1,
B, 1,
C, 2, C= A+B
D, 1,
E, 1,
F, 2, F = D+E
G, 4, G=A +B+C+D
];

t1:
NoConcatenate
load Area, Amount, subfield(mid(Formula, 3),'+') as Formula
Resident source;
drop table source;

left join(t1)

load Formula as Parent, Area as Child, Area as Name

resident t1;

final:
hierarchy(Child, Parent, Name)
load distinct Child, Parent,Name resident t1;
drop table t1;

[colinodonnel]

Thanks for the quick response and apologies for the delay in getting back to you.

The idea in principle is good.

It came unstuck during the join. This needs to be separate out i believe.

Here is the full code

Data:
Load
Area,
Amount,
replace(Formula, ' ','') as Formula
inline [
Area, Amount, Formula
A, 1,
B, 1,
C, 2, C = A+B
D, 1,
E, 1,
F, 2, F = D+E
G, 4, G=A+B+D+E
]
;

// Create adjacent list
AdjacentList:
Load distinct
AutoNumber(Parent) as ParentNo,
Parent as Parent,
Child
;
Load
Area as Parent,
Trim(subfield(SubField(Formula, '=', 2), '+')) as Child
Resident Data
where len(trim(Formula))>0;
Drop table Data;

//Find how many parents there are
AdjacentList_Max:
Load
Max(ParentNo) as ParentNo_Max
Resident AdjacentList;
Let vParentNo = peek('ParentNo_Max', -1, 'AdjacentList_Max');
Drop table AdjacentList_Max;

// Identify additional Parents by intermatching different paretn
For i = 0 to '$(vParentNo)'
NoConcatenate
AdjacentList1:
Load
*
Resident AdjacentList
where ParentNo = '$(vParentNo)';

Left join (AdjacentList1)
Load
Child,
Parent as Child_New
Resident AdjacentList
where ParentNo <> '$(vParentNo)';

Concatenate(AdjacentList)
Load
ParentNo,
Parent,
Child_New as Child
Resident AdjacentList1
where NOT ISNULL(Child_New);
Drop table AdjacentList1;
Next i