Dynamic Filtering

[nicktekippe]

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!

[jzkyburz · Accepted solution]

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:

[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?

[Emmanuel_G]

Hi @nicktekippe ,

Find attached an easy way to do this.

[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.

[jzkyburz]

@nicktekippe , did you try the sample tool to see if that meets your needs? That will keep all columns in the mix.

[Qiu]

@nicktekippe 
Hope I get you correctly.