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

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

Mon, 20 Aug 2012 21:04:43 GMT

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?

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

manojkvrajan — Mon, 20 Aug 2012 21:24:18 GMT

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;

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

rwunderlich — Tue, 21 Aug 2012 04:28:43 GMT

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

;