topic Re: Remove duplicate rows based on the highest value in another field in QlikView

Remove duplicate rows based on the highest value in another field

qlikmark1990 — Tue, 12 Jun 2018 12:50:45 GMT

I have a dataset that contains patient data and its postcodes. The geographical coordinates are extracted from another table with postal code and coordinates data. By joining the tables together, there are some postal codes that has 2 or more possible latitude and longitude coordinates. My goal is to only keep the records that has the highest value in the field 'longitude and latitude'.

search Id	postal code	latitude	longitude	longitude and latitude
2864	NL\|1717KK	51,55811332	5,096711236	5155850967
2572	NL\|8888TN	50,87629953	5,979115693	5087659791
53	NL\|7777RD	51,83133474	5,810670978	5183158106
53	NL\|7777RD	51,83321971	5,813177268	5183358131
1047	NL\|2323MD	51,06767516	5,853132061	5106758531

I made the field 'longitude and latitude' as a combination of the fields 'latitude' and 'longitude' to use as a value to determine a max.

How can I process the data to remove only the row

NL|7777RD

51,83133474

5,810670978

5183158106

that has the lowest value and keep the one with the highest value? The method also needs to work if there are 3 of more rows with same search Id and if double search Id's have the same longitude and latitude.

Using DISTINCT while loading does't make sense.

This data isn't real to keep the original data private.

Re: Remove duplicate rows based on the highest value in another field

sunny_talwar — Tue, 12 Jun 2018 12:53:48 GMT

May be this

Table:

LOAD [search Id],

[postal code],

latitude,

longitude,

[longitude and latitude]

FROM ...;

Right Join (Table)

LOAD [search Id],

[postal code],

Max([longitude and latitude]) as [longitude and latitude]

Resident Table

Group By [search Id], [postal code];

Re: Remove duplicate rows based on the highest value in another field

sunny_talwar — Tue, 12 Jun 2018 13:01:49 GMT

Here is a sample script

Table:

LOAD * INLINE [

search Id, postal code, latitude, longitude, longitude and latitude,

2864, NL|1717KK, "51,55811332", "5,096711236", 5155850967,

2572, NL|8888TN, "50,87629953", "5,979115693", 5087659791

53, NL|7777RD, "51,83133474", "5,810670978", 5183158106,

53, NL|7777RD, "51,83321971", "5,813177268", 5183358131,

1047, NL|2323MD, "51,06767516", "5,853132061", 5106758531

];

Right Join (Table)

LOAD [search Id],

[postal code],

Max([longitude and latitude]) as [longitude and latitude]

Resident Table

Group By [search Id], [postal code];