convert rows to columns based on a logic

[chaithuj]

I am stuck need urgent help with a problem that I am trying to solve.
Any suggestions would be really helpful.

Sample Data
Kmime_Sample_Data.xlsx (12.8 KB)
Kmime_Sample_Data.xlsx (12.8 KB)

I have Data for employees who can be part of multiple Affiliates(companies).We have a total of 4 affiliates.
I have to create a solution to identify employees who are part of multiple affiliates and create a report.

For Example, if an employee is part of 4 affiliates, he will have 4 rows and each row has data for that affiliate in 4 columns Emp_Status, High_Comp_Ind, Hire_Date, Term_Date.
Each employee is uniquely identified using 3 columns ssn. first name, last name
Affiliate info can be got from 2 columns FEIN, AFFILIATE

Now The challenge I have is to show that this employee is part of 4 affiliates by brining all 4 rows into one row and all the affiliate data from 4 rows into different columns.
When we bring row data into columns, we also change the column names to represent data for that Affiliate.
If Affiliate is AH, columns would now change as
AH_EMP_Status, AH_HIGH_COMP_IND, AH_DOH, AH_DOT
we also add an additional column AH_Relation and add 'Y" if he is part of that Affiliate and N if not part of that Affilaite.
If its N, we will leave all other columns related to that Affilaite as Blank.
If its Y, we bring values from the other rows into these columns

[Daniel_Weikert]

hi
with a groupby node on SSN you can get your employees into one row. Depending on the aggregation for th columns e.g. Concatenate you could then use cell splitter node to get the data into single columns. Just as a starting idea.
br

[chaithuj]

Thank You tried Group by and got values in list and split them into multiple columns and applied Logic.
Was Looking at if we have any easier way of doing it.
I have to add lot of column expressions, rule engine nodes to remove the data being added when used list

[hmfa]

Hi @chaithuj

Good to know you got a solution.
I played a little with it and have this one

Try it.
rows_to_columns.knwf (152.6 KB)
Br

[chaithuj]

Thank you very much, will try this

[chaithuj]

Awesome, this is Brilliant. I got solution but I was not happy with the amount of work that went into it and the number of nodes I used.
Thank you very much for your time and efforts