Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

simonsiplak
New 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

Re: Create new column with group by in Load script

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;
2 Replies

Re: Create new column with group by in Load script

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;
Highlighted
simonsiplak
New Contributor II

Re: Create new column with group by in Load script

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 🙂