Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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

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

IDProjectDate StartedStatus
1Project12019-03-15Initiated
2Project22018-08-23Reviewed
3Project32019-02-01Final

 

Table 2

IDUser RoleUserE-mail
1AdminUser1user1@user1.com
1Project ManagerUser2user2@user2.com
1AnalystUser3user3@user3.com
1AnalystUser5user5@user5.com
2Project ManagerUser2user2@user2.com
2AnalystUser4user4@user4.com
3Project ManagerUser6user6@user6.com
3AnalystUser3user3@user3.com
3AdminUser7user7@user7.com

 

Desired Result

IDProjectDate StartedStatusUserE-mail
1Project12019-03-15InitiatedUser3, User5user3@user3.com, user5@user5.com
2Project22018-08-23Reviewed(Missing)(Missing)
3Project32019-02-01FinalUser6user6@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.

Labels (1)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Try this:

Pick(

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

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

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

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

)

View solution in original post

4 Replies
Highlighted
Partner
Partner

Try this:

Pick(

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

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

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

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

)

View solution in original post

Highlighted
Creator
Creator

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!

Highlighted
Partner
Partner

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

Highlighted
Creator
Creator

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!