Create new column with group by in Load script

[simonsiplak]

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:

Output should be:

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?

[sunny_talwar · Accepted solution]

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]

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 🙂