Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

removing rows based off criteria in other rows

jam1531
5 - Atom

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_DATEStore#ITEM #DESCRIPTIONCases_Out
2/13/20206042255798MJ SPICY CRAB ROLL54
2/12/20206042255798MJ SPICY CRAB ROLL25
2/11/20206042255798MJ SPICY CRAB ROLL4
2/10/20206042255798MJ SPICY CRAB ROLL0
2/13/20206042144971145MJ PHILLY ROLL3
2/12/20206042144971145MJ PHILLY ROLL0
2/11/20206042144971145MJ PHILLY ROLL0
2/10/20206042144971145MJ PHILLY ROLL1
2/13/2020604254487CJ POPEYE0
2/12/2020604254487CJ POPEYE0
2/11/2020604254487CJ POPEYE0
2/10/2020604254487CJ POPEYE5

 

and come out presented like...

 

Store#ITEM #DESCRIPTIONCONSECUTIVE DAYS OUT
6042255798MJ SPICY CRAB ROLL3
6042144971145MJ PHILLY ROLL1

 

Important to note:

  1. 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.
  2. 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.
  3. 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.

2 REPLIES 2
fmvizcaino
17 - Castor
17 - Castor

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

jam1531
5 - Atom

PERFECT!!! Thanks so much!

Labels