How do i compare columns from different rows based on a rule and output the results to a new column

[chaithuj]

I have a Participants Table for 401k plan and each participant can appear one to 3 times based on number of plans he is eligible. Data validation rule is based on 2 columns seq number (11,13,14) and indicator (MCH,FCC,Y or N). T
My problem is I need to loop thru a participant based on his ssn. I need to look at 2 columns for each row and apply this formula
If seq no =11, take what is present in the indicator column and write it to a new column “Eligible Match”
This is direct rule.
The trick is with second part.we have to check 2 rows and combine to one row based on certain rules as below

If seq no =14 and indicator =Y → see if this participant has a row present with seq no 13.
if row 13 is present read this indicator value and output to the new column “Eligible Match”
so the 2 rows will be gone and we should have only one row in output with seq no 13 and corresponding indicator value copied to “Eligible Match”

If seq no =14 and indicator =Y and this participant dont have a second row present with seq id =13
if row with seq 13 is not present, then output should be as
seq id =13 and “Eligible match” column we should show as blank (empty)

if seq no =14 and indicator =‘N’
then check if "seq no"13 is present , if present then copy this “indicator” column value to “Eligible Match”

if seq no =14 and indicator =‘N’
then check if "seq no"13 is not present ,don’t copy this row.
no entry will be made for this participant

can anyone please help on how we can achieve this

[AlexanderFillbrunn]

Hi @chaithuj,
I would first use two parallel Row Filter nodes to get all rows with seq no 14 in one table and all with seq no 13 in another table. Then you can do a left outer join using the Joiner node (based on user id) to get all pairs of seq 14 and 13, with the values for 13 being missing if there is no corresponding match. Now you can use a Rule Engine to encode your rules as you described them above.
Kind regards,
Alexander

[chaithuj]

Thank you very much, will check and get back if I have further questions