topic Remove duplicates in QlikView Script in QlikView

Remove duplicates in QlikView Script

GeorgePhilips2302 — Tue, 24 Jan 2023 09:00:33 GMT

City field in the attached file has duplicate values with different geographykey values,

I want to remove the duplicates and keep one unique city with any one geographykey.

Variety of solutions expected!

Re: Remove duplicates in QlikView Script

QFabian — Tue, 24 Jan 2023 13:07:10 GMT

Hi @GeorgePhilips2302 , please check this.

In this case, there is no duplicate, because they are different State

116	Augsburg	Bayern	Germany
137	Augsburg	Hamburg	Germany

But for the rest works properly, or do you need not to consider the field State for the duplicates?

This is the Script that i made for you :

Data:
LOAD distinct
City,
State,
Country,
min(GeographyKey) as GeographyKey
FROM [..\Users\Qlik\Desktop\Remove duplicates.xls]
(biff, embedded labels, table is Sheet1$)
Group By
City,
State,
Country;

Re: Remove duplicates in QlikView Script

GeorgePhilips2302 — Tue, 24 Jan 2023 13:33:59 GMT

This works but the geographykey which had duplicates are now showing null, how can i remove them off completely, Refer attached file.

Re: Remove duplicates in QlikView Script

QFabian — Tue, 24 Jan 2023 14:08:01 GMT

@GeorgePhilips2302 maybe that values comes from another tabla with that field, making a key ?

Then you can hide that values in your chart

Re: Remove duplicates in QlikView Script

GeorgePhilips2302 — Wed, 25 Jan 2023 04:53:19 GMT

Okay, Thank you buddy!