removing rows based off criteria in other rows

[jam1531]

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:

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.

[fmvizcaino · Accepted solution]

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]

PERFECT!!! Thanks so much!