Build a hierarchy with additional information on the link between two columns (or another solution other than with a hierarchy)

[Obsyky]

Hello,

I'd like to explain what I mean.

I have a table of links between objects that can tell me the date on which the link was created. I want to build a hierarchy where I can find out the date of the link between each level of hierarchy.

The ultimate goal (if other solutions are possible) is to be able to know the maximum link date between two levels.

I'll give an example:

Link table :

A1	B1	15/05/2024
B1	C1	14/05/2024
C1	D1	16/05/2024
A2	B1	13/05/2024
A3	B3	19/05/2024
B3	C1	20/05/2024
A1	B4	05/05/2024
B4	C4	06/05/2024
C4	D1	07/05/2024

I then have a gender table which tells me between whom and whom I should look for the max date:

A1	B1
A1	D1
A2	C1
B3	D1
A1	C4
B4	D1

And what I would like at the end is this :

A1	B1	15/05/2024
A1	D1	16/05/2024
A2	C1	14/05/2024
B3	D1	20/05/2024
A1	C4	06/05/2024
B4	D1	07/05/2024

If you have the slightest solution to my problem, I'll be incredibly grateful.TY

[F_B]

Hi @Obsyky ,

is this what you are looking for?

//If your LinkTable contains the fields From, To, LinkDate

//and your GenderTable contains the fields Start, End

//create a path table:

PathTable:
LOAD
Start,
End,
Start AS CurrentNode,
'' AS Path,
Date('01/01/1900') AS MaxLinkDate
RESIDENT GenderTable;

LET vContinue = 1;

DO WHILE $(vContinue)
LEFT JOIN (PathTable)
LOAD
Start,
End,
From AS CurrentNode,
Max(LinkDate) AS MaxLinkDate
RESIDENT LinkTable
WHERE EXISTS(CurrentNode, To)
GROUP BY Start, End, From;

JOIN (PathTable)
LOAD DISTINCT
Start,
End,
From AS CurrentNode,
From & '-' & CurrentNode AS Path
RESIDENT LinkTable
WHERE EXISTS(CurrentNode, To);

LET vContinue = NoOfRows('PathTable') - NoOfRows('GenderTable');
LOOP

DROP FIELD CurrentNode;

//Calculate the maximum link date for each pair in the gender table

FinalTable:
LOAD
Start,
End,
Date(Max(MaxLinkDate)) AS MaxLinkDate
RESIDENT PathTable
GROUP BY Start, End;

DROP TABLE PathTable;