Have multiple records with same field value. Only want one record per distinct field value.

[#1]

Hi community,  I have a field say social security number that has mulitple records in the table due to different employee numbers or name changes.  I only want to load one record, I don't care which, per social security number.  Any ideas how to do that?

[manojkvrajan · Accepted solution]

Brandon, Do you have an 'Update Time' for these records? Please follow this logic if Yes and let me know if this works.

Temp:

LOAD DISTINCT SSN,

max ([Updated Time]) AS UpdatedTime

FROM xyz.qvd

GROUP BY SSN;

Key:

LOAD *,

SSN&'_'&UpdatedTime AS Key

RESIDENT Temp;

DROP TABLE Temp;

ActualTable:

LOAD *,

SSN&'_'&UpdatedTime AS Key

WHERE EXISTS (Key, SSN&'_'UpdatedTime);

DROP TABLE Key;

DROP FIELD Key;

[rwunderlich]

Here's one approach.

data:

LOAD *, RecNo() as RecId INLINE [

SSN, Name

1234, Rob

1234, Rob W

4567, Jane

7890, Joe

7890, Joey

]

;

Inner JOIN (data)

LOAD min(RecId) as RecId

RESIDENT data

GROUP By SSN

;