Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
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!