Search for a match from two fields

[D19PAL]

I know, it's hard to explain 

ID,BOX1,BOX2,TEST 

1,1,2,69

2,1,2,67

3,1,2,56

4,2,3,69

5,2,3,43

6,2,3,77

7,3,4,88

8,3,4,3r

This is hard to explain if box 1's box2 has the same test.

So in this example we've got 1 match.

ID 1 and ID 4 both have the same test.

[stevejoyce]

Can you give some more sample data.  this doesn't make senes, as employid, teacher, class are all on same row.  this would imply every row would come back with a 1?

[D19PAL]

I know, it's hard to explain 

ID,BOX1,BOX2,TEST 

1,1,2,69

2,1,2,67

3,1,2,56

4,2,3,69

5,2,3,43

6,2,3,77

7,3,4,88

8,3,4,3r

This is hard to explain if box 1's box2 has the same test.

So in this example we've got 1 match.

ID 1 and ID 4 both have the same test.

[stevejoyce]

Still not clear what you are trying to say.  Looks like you edited your original post so now we lost that info also.  You want to aggr(count( ID), TEST)?  Not sure how box1 & box2 come into play.

[D19PAL]

Sorry, it is hard to word, I've done it and will post how tomorrow.

[Saravanan_Desingh]

Are u looking something like this? I have added additional data for testing.

tab1:
LOAD * INLINE [
ID,BOX1,BOX2,TEST 
1,1,2,69
2,1,2,67
3,1,2,56
4,2,3,69
5,2,3,43
6,2,3,77
7,3,4,88
8,3,4,3r
9,3,4,77
];

Left Join(tab1)
LOAD ID As _ID, BOX2 As BOX1, TEST As TEST2
Resident tab1;

Left Join(tab1)
LOAD ID, If(Concat(DISTINCT (TEST = TEST2))=-10,'Y','N') As Flag
Resident tab1
Group By ID;

tab2:
LOAD ID, BOX1, BOX2, TEST, If(TEST = TEST2, _ID) As Match_ID,-(TEST = TEST2) As Result, Flag
Resident tab1
Where (Flag='Y' And (TEST = TEST2)=-1) Or Flag='N';

Drop Table tab1;