Show employees with more than one ID

[D19PAL]

Hi,

I only want to see the rows with employees with more than one records and each record.

ID, Employees

1,A

2,A

3,B

4,C

5,C

6,F

So my outcome I want is.

ID,Employees 

1,A

2,A

4,C

5,C

In expression or code.

Thanks

[vchuprina]

Hi,

You can make resident load from your table and count the number of ID for each employee.

After this in the preceding load filter data by condition IDPerEmployee > 1, and join (inner join) table to the original table.

Try script below:

DATA:
LOAD * Inline[
ID, Employees
1,A
2,A
3,B
4,C
5,C
6,F
];

Inner Join(DATA)
LOAD
   Employees
  Where IDPerEmployee > 1;
LOAD
   Count(ID) AS IDPerEmployee,
   Employees
Resident DATA
Group BY Employees
;

Regards,

Vitalii

[D19PAL]

Thanks, any way to do in the frontend,

[D19PAL]

Thinking about it, my data model is already too complicated as it is.

Can this be done in the front end?

[chaorenzhu]

if(aggr(nodistinct count(ID),Employees)>1,ID)

[vchuprina · Accepted solution]

Hi,

You can add a flag in your script and then use it at the front end in your expressions.

Sum({<Flag = {1}>} Value)

DATA:
LOAD * Inline[
ID, Employees
1,A
2,A
3,B
4,C
5,C
6,F
];

LeftJoin(DATA)
LOAD
   Employees,

 '1' AS Flag
  Where IDPerEmployee > 1;
LOAD
   Count(ID) AS IDPerEmployee,
   Employees
Resident DATA
Group BY Employees;

Also, you can try the following expression

sum( {$ <Employees={"=count(ID)>1"} >} Value)

It's not a good idea to use if and aggr functions, because both of them are expensive from a performance point of view, so you should always try to avoid them.

Thanks,

VItalii

[vinieme12]

Just this is enough to see duplicates in front end

Dimension=

ID and Employee

Expression = 

if(count(Total  <Employee> Employee)>1,count(Total <Employee> Employee))

[D19PAL]

It works with dummy data.

But with real data,  I've got multiple rows for ID so they are all showing over 1.

[vchuprina]

Try to use count(distinct ID) instead of count(ID)

[tresesco]

This?

Concat(DISTINCT {<Employees={"=Count(Employees)>1"}>}Employees)