Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Check out our powerful new search update! You can read more here. Please let us know if you have any feedback by creating a topic here.

removing rows based off criteria in other rows

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

Highlighted
Alteryx Certified Partner

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

Labels