Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have data in a table like this.
Within the load script I need to filter out or flag the lowest UserID where their GMC#s match. I need to keep the records that have no UserID as well.
Rec# | UserID | CompID | GMC# |
---|---|---|---|
1 | 123 | 456 | 123456 |
2 | 789 | 456 | 123456 |
3 | 987 | 234567 | |
4 | 1234 | 345678 | |
5 | 321 | 987654 | |
6 | 987 | 987654 |
So from the above data i would end up with Rec#s 2,3,4,6 with Rec# 1 & 5 either filtered out or flagged
I was trying to use an approach like the one below but i end up losing the records with no UserID
Inner Join (tbl1)
LOAD
GMC#
,Max(UserID) as UserID
Resident tbl1
Group By GMC#;
Any Ideas how best to approach this much appreciated
Try like this to flag your records:
T1:
LOAD * FROM YourTable;
LEFT JOIN
LOAD
min(UserID) as UserID,
[GMC#],
1 as Flag
RESIDENT T1
GROUP BY [GMC#];
Try like this to flag your records:
T1:
LOAD * FROM YourTable;
LEFT JOIN
LOAD
min(UserID) as UserID,
[GMC#],
1 as Flag
RESIDENT T1
GROUP BY [GMC#];
Thanks for reply - that works although i've now realised my data is more complex than i thought