Simple join of 2 resident tables into new table

[QlikAldo]

Hi guys,

Appreciated if I can get some help here. I have the following scenario in my script:

//User id's per task id
Task.UserIds:
LOAD * INLINE [
Task.UserIds.TaskId, UserIdKEY
'TASKIDA', 'USERIDA'
'TASKIDA', 'USERIDB'
'TASKIDB', 'USERIDC'
'TASKIDC', 'USERIDA'
];

//User data per user id
User.Data:
LOAD * INLINE [
UserIdKEY, User.Data.UserDesc
'USERIDA', 'User ID A Description'
'USERIDB', 'User ID B Description'
'USERIDC', 'User ID C Description'
'USERIDD', 'User ID D Description'
];

Now I'd like my result table to have all: "User.Data.Userdesc"-values concatenated into one field per unique: "Task.UserIds.TaskId". Probably quite simple for you guys but I can't seem to figure it out.

I hope you can help me with this.

Thanks in advance!

[MarcoWedel]

how would the result table look like for your example?

[QlikAldo]

Hi Marco,

Something like this:

TaskId	User description
TASKIDA	User ID A Description, User ID B Description
TASKIDB	User ID C Description
TASKIDC	User ID A Description

[avkeep01 · Accepted solution]

Hi @QlikAldo , 

You could try the following: 

//User data per user id
User.Data:
MAPPING LOAD * INLINE [
UserIdKEY, User.Data.UserDesc
'USERIDA', 'User ID A Description'
'USERIDB', 'User ID B Description'
'USERIDC', 'User ID C Description'
'USERIDD', 'User ID D Description'
];

//User id's per task id
temp01_Task.UserIds:
LOAD DISTINCT
Task.UserIds.TaskId,
APPLYMAP('User.Data',UserIdKEY,'Unknown') AS User.Data.UserDesc
INLINE [
Task.UserIds.TaskId, UserIdKEY
'TASKIDA', 'USERIDA'
'TASKIDA', 'USERIDA'
'TASKIDA', 'USERIDB'
'TASKIDB', 'USERIDC'
'TASKIDC', 'USERIDA'
];

Task.UserIds:
NOCONCATENATE LOAD 
Task.UserIds.TaskId,
CONCAT(User.Data.UserDesc,', ') AS User.Data.UserDesc
RESIDENT temp01_Task.UserIds
GROUP BY Task.UserIds.TaskId;

DROP TABLE temp01_Task.UserIds; 

[QlikAldo]

Thanks a lot Avkeep! 
That worked.

Now I will study this a bit so I will understand why as well hahah.
Very appreciated!