Remove duplicate an leave higher value

[#1]

Hello,

I am having trouble removing the duplicates from my load. I know I am able to remove duplicates by using the distinct load, but that doesn't allow me to pick wich values are the ones that stay.

Thanks!

[marcus_malinow · Accepted solution]

Hi Sara,

one technique might be to calculate the value field you are interested in, and JOIN this back on to your original table. For example:

OriginalTable:

LOAD

    key,

    value,

    etc, etc....

FROM

[wherever your original source is];

INNER JOIN (OriginalTable)

LOAD

    key,

    max(value) as value

RESIDENT OriginalTable

GROUP BY key;