Want only rows if two values are the same

[D19PAL]

Hi Guys,

I only want to see the rows where employee and manager have the same company.  But only want to count the employee rows not manager.

ID,COMPANY, EMPLOYEE,MANAGER

1,TESCO, MARK,PAUL

2,TESCO,PAUL,BARRY

3,MORRISONS,CHRIS,RACHEL 

4,MORRISONS,RACHEL,JOHN

5,MORRISONS,HARRY,BARRY

So count or display only ID 1 & 4 

So count of 2.

Thanks

[Habari]

Hi,

You can use a inner join to filter the employee with manager in the same company.

Employees:
load * inline [
ID,COMPANY, EMPLOYEE,MANAGER
1,TESCO, MARK,PAUL
2,TESCO,PAUL,BARRY
3,MORRISONS,CHRIS,RACHEL
4,MORRISONS,RACHEL,JOHN
5,MORRISONS,HARRY,BARRY
];

inner join(Employees)
load
COMPANY,
EMPLOYEE AS MANAGER
Resident Employees;

This will filter out 1 and 3.

[D19PAL]

Ok, how about in the front end? Is there a way to do it in front end and not the script.

[D19PAL]

But I've got other fields in that table that I need, this is a snip it of the full table.

By doing the inner join, I'll lose everything else? Or will they remain 

[Habari · Accepted solution]

Option A - Innerjoin
With the inner join all fields from the original table remain in the resulting table. (The inner join filters out the records that do not have a match)

Option B - Indicator 
An other way is to add an indicator to to the original table with a left join. 

left join(Employees)
load
    COMPANY,
    EMPLOYEE AS MANAGER,
    1 as _ManagerWorksForSameCompany
Resident Employees;

Now you can create a table in the frontend with this indicator;

• sum(_ManagerWorksForSameCompany)
  or
• count({<_ManagerWorksForSameCompany={1}>}ID). 

Option C - CompositeKeys
An other way is to add compositekeys in the employee table and use the p function in set analysis; 

Script: 

Employees:
load *,
     COMPANY & '|' & EMPLOYEE as CompanyEmployee,
     COMPANY & '|' & MANAGER as CompanyManager
inline [
     ID,COMPANY, EMPLOYEE,MANAGER
     1,TESCO, MARK,PAUL
     2,TESCO,PAUL,BARRY
     3,MORRISONS,CHRIS,RACHEL
     4,MORRISONS,RACHEL,JOHN
     5,MORRISONS,HARRY,BARRY
];

Expression in frontend:

• count(distinct {<CompanyManager=p(CompanyEmployee)>}ID)

[D19PAL]

Many thanks