Parent nodes, leaf nodes Join

Question

Hi community!!

I have two tables

1. table_role : it has two columns employee name, role

employee_name role

x                         admin

y                         analyst

z                         creater

m                       site admin

2. table_employee : it has two columns employee name, manager_name

employee_name manager_name

x                              y

y                             z

z                             m

m                           null

i want to produce an output where each manager has his role as well all the roles concatinaed of his associated employee under him

output table:

employee, role

x                admin

y         analyst,admin

z          creator, analyst,admin

m            admin,analyst, creator, site admin

ScottLewis · Answer

Interesting problem. Would make a good weekly challenge.

Solution uses an iterative macro to find all the manager/employee pairs, joins with the role table and then uses summarize/concatenate to put together the role list for each manager.

You could do a version where the iterative macro builds the role string but I like this version because it focuses on the real relationships between the data (A reports to B)  and tacks on the informational field (role) at the end. More of a design preference than a rule.

EmployeeRoles.yxmd

EmployeeRolesMacro.yxmc