Retrieve data based on tree structure inside a column

[Hugror]

Hi,

I'm trying to create a new column, "Parent", based on the 2 columns X and Y.

It's kind of a basic tree structure in Y. 2 is the daughter of 1 and so on, as in the example below.

In the "Parent", we use the Y column, but with the X value.

I really don't know how to do that, I tried to do it with a formula/windows recipe but i didn't succeed.

Hope I was clear enough. Thanks for your help.

X	Y	Parent
A	1
B	2	A
C	2	A
D	1
E	2	D
F	2	D
G	3	E (or F based on some conditions)
H	1

---

Operating system used: Windows 11

[Turribeach]

This should be possible as long as you can guarantee that the sorted order can be applied as SORT BY X,Y. In other words the data allows us to sort it in such way that returns the data ordered in the same way as shown in your sample. Finally the logic doesn't add up for G. According to the other rows G's parent should be D not E or F.

[Hugror]

My data is sorted exactly as shown in my sample. The only thing is that Y can go from 1 to 9 and X is a random string

[Turribeach]

What I need to be sure is that the data is sortable not sorted. These are different things. You can use Window recipe to calculate a parent but for that to work you need to specify a sort order. So it doesn't matter what sort order the data comes in, what matters is if I can sort by columns X and Y and obtain the same result. With regards to G I can't help unless I understand the rule of how you want the logic to work. For all the other rows you want to populate with the Level 1 parent X value when Y (level) >1. So that's a rule I can work with. With G I don't reallty know what you want.