Matching 3 values from rows

[D19PAL]

Hi Guys,

How do I create a flag for ID that matches three columns and values.

SALESID, ID, COLOUR, SHAPE, SIZE

1,1, RED,SQUARE,SMALL

2,1, RED,SQUARE,LARGE

3,2, BLUE,TRIANGLE,LARGE

4,3,RED,SQUARE,SMALL

5,3,RED,SQUARE,MEDIUM

6,3,RED,SQUARE,MEDIUM

So I want something in the script to let me do this in in a table.

SALESUD,count of matches

1,1

4,1

5,1

6,1

Thanks

[anat]

create a key by combining COLOUR, SHAPE, SIZE

then take count of Key,if count is >1 means same entry,u can consider those records

[D19PAL]

This is sample data, they will necessarily not be on the same rows, I'll change it.

Sorry 

[D19PAL]

Sorry, after reading your reply, thanks, I've realised I've wrote my requirements wrong so I've edit it.

[D19PAL]

Anyone got any ideas?

[anat · Accepted solution]

test:

load * ,COLOUR&SHAPE&SIZE as Key;

load * inline [

SALESID, ID, COLOUR, SHAPE, SIZE

1,1, RED,SQUARE,SMALL

2,1, RED,SQUARE,LARGE

3,2, BLUE,TRIANGLE,LARGE

4,3,RED,SQUARE,SMALL

5,3,RED,SQUARE,MEDIUM

6,3,RED,SQUARE,MEDIUM

];

noconcatenate

load Key,count(Key) ,if(count(Key)=2,1)resident test group by Key;

[D19PAL]

It's not working, somehow some rows are duplicating and I'm not getting the correct results.