Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
simonsiplak
Contributor II
Contributor II

Create new column with group by in Load script

Hello,

I need to generate new column "Subadmins_count" in load script with "group by" (or different method) of column "parent_id". It should be count of parent_id. Something line table.groupby('parent_id).count() and add this to existing table.

Input:

Screen Shot 2019-04-17 at 4.03.01 PM.png

Output should be:

Screen Shot 2019-04-17 at 4.03.17 PM.png

So far I have this code but it isn't working. With group by in QLIK I lost user_id

Users:
LOAD
    user_id,
    login,
    parent_id 
FROM [lib://Users.QVD] (qvd);

ParentTMP:
LOAD
    count(login) as Subadmins_count
resident Users Group By parent_id;

Any idea how to solve this "simple" thing?

1 Solution

Accepted Solutions
sunny_talwar

Try this

Users:
LOAD
    user_id,
    login,
    parent_id 
FROM [lib://Users.QVD] (qvd);

ParentTMP:
LOAD parent_id,
    count(login) as Subadmins_count
Resident Users
Group By parent_id;

View solution in original post

2 Replies
sunny_talwar

Try this

Users:
LOAD
    user_id,
    login,
    parent_id 
FROM [lib://Users.QVD] (qvd);

ParentTMP:
LOAD parent_id,
    count(login) as Subadmins_count
Resident Users
Group By parent_id;
simonsiplak
Contributor II
Contributor II
Author

Thanks, it's working. I thought that if you are doing group by function you have to only use aggregation functions like sum, count etc. I made mapping table from ParentTMP (called MapSubadminsCountToUser) and then in Users I used this line

ApplyMap('MapSubadminsCountToUser', user_id, null()) as [Subadmins_count]

it's working but I think it's strange solution somehow 🙂