Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
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;
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 🙂