Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
This is sample data, they will necessarily not be on the same rows, I'll change it.
Sorry
Sorry, after reading your reply, thanks, I've realised I've wrote my requirements wrong so I've edit it.
Anyone got any ideas?
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;
It's not working, somehow some rows are duplicating and I'm not getting the correct results.