topic Removing Duplicates in QlikView in QlikView

Removing Duplicates in QlikView

camh99 — Thu, 12 May 2022 16:01:25 GMT

Hi,

I was wondering if anyone might be able to help with an issue I'm having...

In essence, I've got a list of (almost entirely unique) IDs and details loaded in from a QVD, and I'd like to remove any IDs which appear more than once.

For example, I'd want the table below:

ID	Position	Salary
1	CEO	80000
2	Analyst	30000
2	Programmer	35000
3	HR	25000

to appear as:

ID	Position	Salary
1	CEO	80000
3	HR	25000

I'm sure there's a simple solution but I'm struggling to get QV to do what I want.

Any help is much appreciated!

Re: Removing Duplicates in QlikView

hic — Thu, 12 May 2022 16:29:05 GMT

You need to do this in two passes. One solution could be

// === Script 1
UniqueIDs:
Load * Where Count=1;
Load ID as UniqueID, Count(ID) as Count From <Source> Group By ID;

Data:
Load * From <Source> Where Exists(UniqueID,ID);

Drop Table UniqueIDs;
// === Script 2

Another Could be

// === Script 2
Data:
Load * From <Source>;

Inner Join (Data)
Load * Where Count=1;
Load ID, Count(ID) as Count From <Source> Group By ID;
// === Script 2

Re: Removing Duplicates in QlikView

camh99 — Wed, 25 May 2022 07:51:02 GMT

Perfect, thank you Henric!