Match a value from another column anywhere in the row

[D19PAL]

I need to see if a student and teacher have the same company.

So employee 1122 has 4 companies and his teacher is employee 167, he also has 4 companies, they share Asda as the same one.thats what I need a count of.

And in the same dept

Form,student, teacher,company, dept

1,1122,167,tesco,a

2,1122,167,sainsbury,a

3,1122,167,Morrison,a

4,1122,167,asda,a

5,167,113,asda,a

6,167,113,comment,a

7,167,113,taser,a

8,167,113,foxy,a

9,113,167,asda,a

10,113,167,roxy,a

11,113,167,troxy,a

12,113,167,foxy,a

13,114,167,comment,b

14,114,167,black,b

15,114,167,blue,b

16,114,167,red,b

So a count of 3, same company for students and teacher

Asda,comment and foxy

1122,114 &113

Thanks

[D19PAL]

Any help with this one please?

Thanks

[stevejoyce · Accepted solution]

this should flag records where the teacher has same company as student.  although i see a count of 5.

your details you provided aren't very clear so maybe different data or gap in what you explained.

data:
load * inline [
Form,student, teacher,company, dept

1,1122,167,tesco,a

2,1122,167,sainsbury,a

3,1122,167,Morrison,a

4,1122,167,asda,a

5,167,113,asda,a

6,167,113,comment,a

7,167,113,taser,a

8,167,113,foxy,a

9,113,167,asda,a

10,113,167,roxy,a

11,113,167,troxy,a

12,113,167,foxy,a

13,114,167,comment,b

14,114,167,black,b

15,114,167,blue,b

16,114,167,red,b
]
;

left join (data)
load
student as teacher
,dept
,company
,1 as teacher_match
resident data
;