Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
53 | 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.
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];
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];
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];