Dynamic Filtering

What I need to do is filter the below data to the highest FM and FY for each group of supplier numbers and filter out the other rows. The data has a lot of different suppliers with multiple lines of data. For example the supplier number 1687 I would only want to keep the row with FY 2021 & FM 10.

Any help would be appreciated!

Developer

Developer Tools

Expression

Data Investigation

Accepted answers

jzkyburz

Have you tried using the Sample tool? Select 'First N rows', set N = 1, and then Group by supplier_number. You'll also need to place a sort tool in before taking the samples to ensure you're selecting the highest FM & FY:

Sort and Sample Example.yxmd

All comments

jzkyburz

Sort and Sample Example.yxmd

Qiu

@nicktekippe
We can use the summarize tool as well
but I do have one question.
Say for one supplier, there is month 11 for 2021, but here is also month 12 for 2022. So what would be answer then?

0821-nicktekippe.yxmd

Emmanuel_G

Hi @nicktekippe ,

Find attached an easy way to do this.

Supplier, month and year.yxmd

nicktekippe

@Qiu The result for that would be the month 12 for 2022. For any example it should be taking the most recent data.

nicktekippe

@Qiu & @Emmanuel_G This will work to filter it down but how to I include the rest of the columns in the data as well? This method gets it filtered to the correct FM/FY but I need the rest of my columns to come through unchanged as well.

Qiu

@nicktekippe
Hope I get you correctly.

0821-nicktekippe-r1.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703