topic Re: Remove Duplicates in QlikView

Remove Duplicates

hkg_qlik — Thu, 16 Aug 2012 17:10:37 GMT

Hi,

I have a table which holds two key fields: MemberID and CustomerID.

Before moving forward I want to check for any duplicates in table with a Key combination of MemberID + CustomerID. (Both reside in the same table)

Any thoughts?

Thanks,

Re: Remove Duplicates

Jason_Michaelides — Thu, 16 Aug 2012 17:17:14 GMT

Try aggregating the resident table and joining to itself:

Data:

LOAD

MemberID,

CustomerID,

1 AS Count

From.....;

JOIN (Data)

LOAD

MemberID,

CustomerID,

Sum(Count) AS Freq

Resident Data

Group by MemberID,CustomerID;

That will show you the frequency of each row.

Hope this helps,

Jason

Re: Remove Duplicates

hkg_qlik — Thu, 16 Aug 2012 17:23:57 GMT

I want to flag the duplicates and remove them from the table.

Re: Remove Duplicates

Jason_Michaelides — Thu, 16 Aug 2012 17:29:08 GMT

I assume you want to leave one of each duplicate record behind? Will all other fields also be identical? If not, how do you decide which record to keep?

Re: Remove Duplicates

hkg_qlik — Thu, 16 Aug 2012 17:38:15 GMT

Yes all other fields would be identical. So I can keep any one record of the two or more.

Re: Remove Duplicates

Jason_Michaelides — Thu, 16 Aug 2012 17:42:26 GMT

Then maybe just LOAD DISTINCT in the first place....

Re: Remove Duplicates

hkg_qlik — Thu, 16 Aug 2012 18:50:59 GMT

So something like this:

Load Distinct MemberID&' '&CustomerID as Key,

MemberID,

CustomerID,

From

ABC.qvd

I want to check when the combination of MemberID and CustomerID is repeating in the table.

Re: Remove Duplicates

manojkvrajan — Thu, 16 Aug 2012 19:16:59 GMT

Script mentioned below brings only the data that has the combination > 1. I hope you can take it forward from here to avoid data duplication using the timestamp or anything specific to your requirements. Does it help?

Temp:

LOAD DISTINCT MemberID, CustomerID,
COUNT(MemberID&'_'&CustomerID) AS Count
FROM
(SourceFile.xls )
GROUP BY MemberID, CustomerID;

Key:

LOAD MemberID&'_'&CustomerID AS Key
RESIDENT Temp
WHERE Count >1;

DROP TABLE Temp;

ActualTable:

LOAD *
FROM
(SourceFile)
WHERE EXISTS (Key,MemberID&'_'&CustomerID);

DROP TABLE Key;

Re: Remove Duplicates

Jason_Michaelides — Thu, 16 Aug 2012 20:38:22 GMT

I meant simply:

LOAD DISTINCT * FROM Data.qvd(qvd);

Jason

Re: Remove Duplicates

hkg_qlik — Fri, 17 Aug 2012 12:40:34 GMT

Thank you all for your inputs.

Re: Remove Duplicates

Wed, 14 Aug 2013 09:24:15 GMT

Hi. I want to do the same thing but all other fields are not identical. For example, if i have 2 customer id's, i only want to take that ID which has a higher "worth". Here "worth" is just a field name. Any help?

Thanks

Re: Remove Duplicates

peschu123 — Wed, 14 Aug 2013 09:59:57 GMT

take a look at attached qvw...

Script;

TMP:

LOAD * INLINE [

ID, worth

1, 1

1, 2

2, 1

2, 2

3, 1

4, 1

4, 2

4, 3

4, 4

];

TEST:

Noconcatenate Load

ID, max(worth) as worth

RESIDENT TMP GROUP by ID;

DROP TABLE TMP;

I think Group by clause just works with data from resident table.

Hope it helps.

Regards

- PS -

Re: Remove Duplicates

Wed, 14 Aug 2013 10:41:46 GMT

Thank you very much. Really helpful. I only have the personal edition so can't view your file. Please could you copy and paste the script into this discussion. Also, I have other variables not just id and worth, where do I put these variables in?

Thanks

Re: Remove Duplicates

peschu123 — Wed, 14 Aug 2013 12:00:05 GMT

the script is pasted in my last post...?!

With variables you mean fields(columns)? With more fields you get an error I think.

I can imagine 2 possible ways to get the data for a full table(perhaps someone knows a better solution):

1. Do as described an then do a join with the origin table to get the rest of the fields.But I'm not that confident with joins so I would do it like this:

2. In short: Create a flag field in the origin table which fields should be loaded(load = 1 or 0)

Create a merged field of ID and worth, like "ID|worth" while loading TEST(and origin table) and create a Mapping table with that data like:

RELEVANT_ID_MAP:

MAPPING LOAD

ID|worth,1 as relevant

RESIDENT TEST;

Than load data(fields) from origin table(TMP) with:

sometemptable:

ApplyMap('RELEVANT_ID_MAP',ID|worth,'0') AS load,

And finally load the data in a table with ... RESIDENT sometemptable where load=1;

Look at it more as rough guide not a step by step tutorial...Sorry, I have not the time at the moment. Perhaps later I'll create a sample.

I admit it looks a bit complicated but if you are used to it is really quick and for me it the safest way. Better than joining and not 100% sure what happens to my data, ending up in a mess.

And finally I think there is sure somebody (more advanced) who can do this "better"/easier. But at the moment this works for me pretty well.

regards,

- PS -