Iterative macro

oumaimak

Hello,

My goal is to build a macro that will allow me to put in an input such as :

File_id	File_parent_id
1
2	1
3	2
4	3
7
9
10
465
6789	465
568	6789

And get as a final result :

File_id	File_parent_id
1
2	1
3	1
4	1
7
9
10
465
6789	465
568	465

The idea is to be able to identify the original parent file for each file_id.

Seeing that I never used macros especially the iterative ones, I have some trouble finding the right logic to get to my result.

Highly appreciate any indications and explanations

Stosh

I would probably need more information to be sure that this is the best approach, but based on what you have provided, I would do the following: Perform an outer-join between the table and itself, setting one sides parent equal to the other sides child. What this will do is return parents of parents if they exist. For example, row two would match nothing because 1 has no parent, but row three would return 1 as 2, the parent of row three has it's own parent. Then, if the new parent field is not null, replace the original parent with the new one. Then you push that list back into the loop and iterate until every parent is the absolute base parent. Now, all you need is a way to verify if each new parent is parent-less, as that is when you would want to stop iterating. You can probably achieve this by performing another join like you did initially and if there are no records in the J output, then you know that each parent has no parent of their own. This is a cool problem, I would love to hear about how you get it done. Good luck!