Comma Delimiting Multiple Fields into One Field based on Association and Status

[mskusace]

I have 2 tables that are associated with the ID column. One table has project data and the other table has the users associated with the project. Each project has a status which indicates who has current ownership of the project. I would like a way to show in the table everyone in a certain role associated with the project based on the status. There can be multiple people in the same role, so I would need multiple people to show in one field delimited by a comma.

Table 1

ID	Project	Date Started	Status
1	Project1	2019-03-15	Initiated
2	Project2	2018-08-23	Reviewed
3	Project3	2019-02-01	Final

Table 2

ID	User Role	User	E-mail
1	Admin	User1	user1@user1.com
1	Project Manager	User2	user2@user2.com
1	Analyst	User3	user3@user3.com
1	Analyst	User5	user5@user5.com
2	Project Manager	User2	user2@user2.com
2	Analyst	User4	user4@user4.com
3	Project Manager	User6	user6@user6.com
3	Analyst	User3	user3@user3.com
3	Admin	User7	user7@user7.com

Desired Result

ID	Project	Date Started	Status	User	E-mail
1	Project1	2019-03-15	Initiated	User3, User5	user3@user3.com, user5@user5.com
2	Project2	2018-08-23	Reviewed	(Missing)	(Missing)
3	Project3	2019-02-01	Final	User6	user6@user6.com

Logic

If Status = Initiated, show Analyst

If Status = Reviewed, show Admin

If Status = Final, show Project Manager

If Status = Initiated and there is no Analyst associated with the project, fill in the field with "(Missing)". This is an error catch for me.

Any guidance would be greatly appreciated.

[shubham_singh · Accepted solution]

Try this:

Pick(

Match(Status,'Initiated','Reviewed','Final'),

Concat({<[User Role]={'Analyst'}>}User,','),

Concat({<[User Role]={'Admin'}>}User,','),

Concat({<[User Role]={'Project Manager'}>}User,',')

)

[mskusace]

I can't seem to get it to work. I double checked all spelling, capitalization, and field names also. It says "Invalid Dimension".

UPDATE

I got it to work as a measure. Is there a way to make that a searchable field? It doesn't appear to be at the moment.

Thanks for the reply and help!

[shubham_singh]

You will have to create this field in script to make it searchable.

[mskusace]

Got it. Would I just use the same code you provided for the load script and put an "As [Name_of_field]" there? Do I need to change any of the syntax? I've only done basic editing in the data load editor. I appreciate your help!