removing rows based off criteria in other rows
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello,
I am new to the form. I am really stumped on this one. If in Excel I would just use a VBA loop but not sure how to achieve in Alteryx.
Essentially below is the data that is fed into Alteryx. It is sorted by item, Store, and date. I need it to take this information and spit out a count by store/item of how many consecutive days in a row (starting yesterday) the store/item has had a positive number in Cases_Out.
So the below would feed into Alteryx
BUSINESS_DATE | Store# | ITEM # | DESCRIPTION | Cases_Out |
2/13/2020 | 6042 | 255798 | MJ SPICY CRAB ROLL | 54 |
2/12/2020 | 6042 | 255798 | MJ SPICY CRAB ROLL | 25 |
2/11/2020 | 6042 | 255798 | MJ SPICY CRAB ROLL | 4 |
2/10/2020 | 6042 | 255798 | MJ SPICY CRAB ROLL | 0 |
2/13/2020 | 6042 | 144971145 | MJ PHILLY ROLL | 3 |
2/12/2020 | 6042 | 144971145 | MJ PHILLY ROLL | 0 |
2/11/2020 | 6042 | 144971145 | MJ PHILLY ROLL | 0 |
2/10/2020 | 6042 | 144971145 | MJ PHILLY ROLL | 1 |
2/13/2020 | 6042 | 54487 | CJ POPEYE | 0 |
2/12/2020 | 6042 | 54487 | CJ POPEYE | 0 |
2/11/2020 | 6042 | 54487 | CJ POPEYE | 0 |
2/10/2020 | 6042 | 54487 | CJ POPEYE | 5 |
and come out presented like...
Store# | ITEM # | DESCRIPTION | CONSECUTIVE DAYS OUT |
6042 | 255798 | MJ SPICY CRAB ROLL | 3 |
6042 | 144971145 | MJ PHILLY ROLL | 1 |
Important to note:
- I only want to count consecutive days out starting yesterday. So if Cases_out yesterday was 0 then all rows with that item/store can be removed even if prior days had positive out number.
- The pull only goes back 3 weeks so the business_date column will always start yesterday but will never go farther back then 3 weeks.
- There will be many store/item combinations but I want the above done for every unique item/store combination.
Any help much appreciated. I went down the path of trying to figure this out with a multi-row formula but am beyond myself and need some help.
Solved! Go to Solution.
- Labels:
- Help
- Tips and Tricks
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @jam1531 ,
I'm attaching an example showing how to do it.
Take a look and let me know if this works for you.
Best,
Fernando Vizcaino
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
PERFECT!!! Thanks so much!
