Help with excluding rows of data based on criteria in multiple rows

[cgoad61]

Hello,

I feel like this should be simple but I've tried several different ways to do this and I'm at a loss...

I am attaching a Dataset and a Workflow (although I added my Dataset in the WF in a Text Input).  This workflow is comparing data between 2 years and has example data for 4 "Sub Acct ID" numbers.  The output is basically what I want except that I want it to filter out data for Sub Acct ID's when the DE07 - Description row within the "System Data Fields" column is not empty in the "2024" column and is empty in the "2025" column.  

To hopefully make it more clear, I've also provided a file reflecting the expected end result.

FYI - My actual data and workflow is much larger than this.  Thank you in advance for any assistance you can lend! :)

[Qiu]

@cgoad61 
I can see your expected result doest not include the data for "Sub Acct ID" 18772 and 1239 because they only have the data for year 2024?

So the data has to have pair 2024 and 2025, otherwise exclude?

[cgoad61 · Accepted solution]

Thanks Qiu... while your suggestion did start me thinking in the right direction, there ended up being a bit more to the actual solution.  For anyone interested in what I ended up doing:

1. Used the Summary Tool to group by Sub Acct ID & Concatenate the Sub Acct ID Year

2. Filtered on the Concat field I just created to Contain 2025

3. Join back with the full report

If anyone has a simpler way, please post...

[Qiu]

@cgoad61 
I think there are 2 more thinks we need to consider in the real business case before we move to Alteryx.

• We can not only filter to have 2025, might there is case only containing 2024?
• There are records that can make 2 pairs, example are rows #1 - #4, what is the criteria to make pair so they can be compaired?
  

[cgoad61 · Accepted solution]

Thanks Qiu...  For the purposes of this file it should always included 2025 but it's only necessary to pull in 2024 if a 2025 line also exists so by doing the Concat it could include ID's with only 2024, only 2025 or both 2024 & 2025.  By filtering on 2025 it pulls in the last 2 scenarios but eliminates the items that are only 2024 and solves my problem.