Count of duplicate records as a new column in editor

[Sloane]

Hi everyone, 

I have tried to find a solution to this but to no avail. I want to count the number of duplicated Ids and have this as a new column and then group by the distinct  ID in the qlik sense editor. Any help will be appreciated. 

[BrunPierre · Accepted solution]

Hi, omit the Name field to get the final table.

T1:
LOAD * Inline [
DepartmentID,Name
50,Jake
100,Tom
50,Miha
50,Ben
60,Cassie];

Left Join
LOAD DepartmentID,
Count(DepartmentID) as IDCount

Resident T1
Group By DepartmentID;

[Kushal_Chawda]

@Sloane  you can count the department on resident load and keep that as separate table instead joining which links to actual table like below.

Data:
LOAD * Inline [
DepartmentID,Name
50,Jake
100,Tom
50,Miha
50,Ben
60,Cassie];

ID_count:
LOAD DepartmentID,
Count(DepartmentID) as IDCount
Resident Data
Group by DepartmentID;

If you now add DepartmentID and IDcount into table, you will have unique count of IDs