community Alteryx IO Alteryx.com
alteryx Community
Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Close
Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How to identify specific rows to keep or remove in a group using formula

Althelp
6 - Meteoroid
‎06-30-2022 05:57 PM

I have data consisting of multiple groups shown below. Within each group, I need to identify specific records to keep, and specific ones to remove. I have manually done this in the column Keep or Remove but I want to recreate the last column using a formula.

 

I want to start from the latest date in each group (last row with date in ascending order) and Keep all the rows (starting from the last row in group and moving up) that sum up to the value in column A from bottom up until the entire value of column A is used up. All other remaining rows would be marked as Remove.

 

What I'm looking for is something where it checks the value of column B (in the last row with the latest date) against the value in column A in the same row. If the value of B is less than or equal to value of column A , mark row as Keep if not mark as Remove. Now we move up one row with the next latest date, if value of column B plus the sum of all the previous Keep column B's is less than or equal to the value in column A, mark as Keep, if not mark as Remove. Moving on to the next row up, if the value in column B plus the sum of all the previous Keep column B's is less than or equal to the value in column A, mark as Keep, if not mark as Remove. 

 

Essentially what I am looking for is a formula for the last column that can do what I am describing above for the below data set.

 

See below for an example table:

GroupABDateKeep or Remove?
1325/2/2022Remove
1325/2/2022Remove
1325/4/2022Remove
1325/4/2022Remove
1315/4/2022Keep
1325/11/2022Remove
1325/17/2022Remove
1326/1/2022Remove
1326/7/2022Remove
1326/13/2022Remove
1326/20/2022Remove
1326/24/2022Keep
2415/17/2022Remove
2416/1/2022Keep
2416/7/2022Keep
2446/13/2022Remove
2416/20/2022Keep
2416/24/2022Keep
3216/1/2022Keep
3216/7/2022Keep
3246/13/2022Remove

 

Labels:
Reply
0 Likes

Accepted SolutionSolved! Go to Solution.

10 REPLIES 10
binuacs
20 - Arcturus
‎07-01-2022 12:03 AM

@Althelp trying to understand your logic for keeping records and removing records. Can you explain the logic for keeping the 6/24 and 5/4 records from the group 1?

Reply
0 Likes
DataNath
17 - Castor
‎07-01-2022 01:21 AM

How does this look @Althelp? Workbook attached.

 

DataNath_0-1656663671019.png

 

Date total checks.yxmd
Reply
0 Likes
grazitti_sapna
17 - Castor
‎07-01-2022 02:31 AM

@Althelp, try this workflow, I hope this solves your purpose.

grazitti_sapna_0-1656667897278.png

 

Thanks!

Sapna Gupta
Althelp_workflow.yxmd
Reply
0 Likes
Althelp
6 - Meteoroid
‎07-01-2022 06:43 AM

@binuacs if you look at column B, those 2 rows sum up to 3 (which is the value in column A) when you start from the bottom (last date) and go up. As the value in column A is then used up, you would mark the remaining rows (going up) as remove.

Reply
0 Likes
Althelp
6 - Meteoroid
‎07-01-2022 08:35 AM

@DataNath  @grazitti_sapna  When I applied this to a larger dataset, I realized I haven't accounted for every situation so it's right sometimes but also wrong sometimes. I have to keep the full value in "A" no matter what, so if nothing adds up to it exactly, then i would still need to keep the row or rows starting from the last date in the group that contain the value of "A" even if over the actual value. See below for 3 more groups, the Alteryx result, and what the correct answer should have been. 

 

GroupABDateAlteryx ResultCorrect Answer
4441/24/2022RemoveRemove
4442/28/2022RemoveKeep
4416/22/2022KeepKeep
5141/31/2022RemoveRemove
51212/6/2021RemoveKeep
650556/16/2022RemoveKeep
Reply
0 Likes
Althelp
6 - Meteoroid
‎07-01-2022 08:51 AM

@DataNath @grazitti_sapna Actually, I realized the 3rd row in date 6/22/2022 should be Remove not Keep because the row above it 2/28/2022 is Keeping the full quantity of 4 in A. So use this table instead. 

 

GroupABDateAlteryx ResultCorrect Answer
4441/24/2022RemoveRemove
4442/28/2022RemoveKeep
4416/22/2022KeepRemove
5141/31/2022RemoveRemove
51212/6/2021RemoveKeep
650556/16/2022RemoveKeep
Reply
0 Likes
Althelp
6 - Meteoroid
‎07-01-2022 11:03 AM

@DataNath @grazitti_sapna Would you happen to be able to help with an alternate simpler scenario as well? In this case, I want to start from the last row in the group and keep the rows that where Column B equal or exceed the quantity in Column A. All other rows can be marked as Remove once Column A has been reached or exceeded.

 

GroupABDateKeep or Remove
1325/2/2022Remove
1325/2/2022Remove
1315/4/2022Remove
1325/4/2022Remove
1325/4/2022Remove
1325/11/2022Remove
1325/17/2022Remove
1326/1/2022Remove
1326/7/2022Remove
1326/13/2022Remove
1326/20/2022Keep
1326/24/2022Keep
2415/17/2022Remove
2416/1/2022Remove
2416/7/2022Remove
2446/13/2022Keep
2416/20/2022Keep
2416/24/2022Keep
3216/1/2022Remove
3216/7/2022Remove
3246/13/2022Keep
4441/24/2022Remove
4442/28/2022Keep
4416/22/2022Keep
5141/31/2022Remove
51212/6/2021Keep
650556/16/2022Keep

  

Reply
0 Likes
Althelp
6 - Meteoroid
‎07-01-2022 02:35 PM

@DataNath @grazitti_sapna Would you be able to help with a simpler second scenario? In this case, I simply want to start from the bottom up of each group and "Keep" rows until the sum of B from bottom up equals or exceeds the value of A with all other rows being marked as "Remove". See below.

 

GroupABDateCorrect Answer
1325/2/2022Remove
1325/2/2022Remove
1315/4/2022Remove
1325/4/2022Remove
1325/4/2022Remove
1325/11/2022Remove
1325/17/2022Remove
1326/1/2022Remove
1326/7/2022Remove
1326/13/2022Remove
1326/20/2022Keep
1326/24/2022Keep
2415/17/2022Remove
2416/1/2022Remove
2416/7/2022Remove
2446/13/2022Keep
2416/20/2022Keep
2416/24/2022Keep
3216/1/2022Remove
3216/7/2022Remove
3246/13/2022Keep
4441/24/2022Remove
4442/28/2022Keep
4416/22/2022Keep
5141/31/2022Remove
51212/6/2021Keep
650556/16/2022Keep
Reply
0 Likes
Althelp
6 - Meteoroid
‎07-01-2022 02:42 PM

@DataNath @grazitti_sapna 

Would you also be able to help with a secondary simpler scenario? In this case I simply want to start from the bottom of each group and "Keep" all the rows until the sum of B is either equal to or exceeds the value of A. Once this is reached, the remaining rows going up would be marked as "Remove". See Below - 

 

GroupABDateCorrect Answer
1325/2/2022Remove
1325/2/2022Remove
1315/4/2022Remove
1325/4/2022Remove
1325/4/2022Remove
1325/11/2022Remove
1325/17/2022Remove
1326/1/2022Remove
1326/7/2022Remove
1326/13/2022Remove
1326/20/2022Keep
1326/24/2022Keep
2415/17/2022Remove
2416/1/2022Remove
2416/7/2022Remove
2446/13/2022Keep
2416/20/2022Keep
2416/24/2022Keep
3216/1/2022Remove
3216/7/2022Remove
3246/13/2022Keep
4441/24/2022Remove
4442/28/2022Keep
4416/22/2022Keep
5141/31/2022Remove
51212/6/2021Keep
650556/16/2022Keep
Reply
0 Likes
Labels