Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
QlikAldo
Contributor II
Contributor II

Simple join of 2 resident tables into new table

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!

 

 

 

Labels (1)
1 Solution

Accepted Solutions
avkeep01
Partner - Specialist
Partner - Specialist

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; 

View solution in original post

4 Replies
MarcoWedel

how would the result table look like for your example?

QlikAldo
Contributor II
Contributor II
Author

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
Partner - Specialist
Partner - Specialist

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
Contributor II
Contributor II
Author

Thanks a lot Avkeep! 
That worked.

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