I need to build something in FME where it automatically selects all ID entries and then looks at another column with values and selects the row with the highest value, and delete the other rows with the same ID. in the screenshot i have 2 columns: Risicoprofiel_BEREK and ID, in ID there are multiple entries which i need to select and then find the highest value from the Risicoprofiel_BEREK column.
Â
You can sort by Risicoprofiel_BEREK in descending order and then use a duplicatefilter with a Key attribute of ID to just keep the row with the highest value.
I would go this way:
Use the Sorter transformer to sort by ID and Risicoprofiel_BEREK (descending)
Then use the Sampler (Sample first 1) and group by ID.
The sampled output port will give you the result you need.
Hope this helps.
The simplest method would be to sort the data with a Sorter transformer (sort by Risicoprofiel_BEREK) and then a DuplicateFilter (filter by ID). Then the ID with the highest Risicoprofiel number will pass and the rest are discarded.
As long as you don't mind the records being in a different order, then that would work fine.
Three answers in five minutes all giving the same solution (more or less). I guess it really must be the right way to go!
thanx for your help guys... i got it working! :)
The simplest method would be to sort the data with a Sorter transformer (sort by Risicoprofiel_BEREK) and then a DuplicateFilter (filter by ID). Then the ID with the highest Risicoprofiel number will pass and the rest are discarded.
As long as you don't mind the records being in a different order, then that would work fine.
will this also work if there are like 3 or 4 entries with the same ID? it will always take the highest?
will this also work if there are like 3 or 4 entries with the same ID? it will always take the highest?
Yes, as long as they are sorted from highest to lowest, the first feature with each ID, i.e. the highest, will always be kept