Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

Replicating a =COUNTIF that is between two values

Highlighted
5 - Atom

i.jpg

I’ve got this basic workflow section set up as shown above. Reading the file in, changing around column names, and then splitting the data between two "Chambers". Chamber 1 and Chamber 2 (haven't gone down the chamber 2 branch yet). I created a record of each row and then to make things easier convert the Action field to 1 for in, 0 for out. I’d like to keep a running number of the units in the chamber. I can get about half way there using the running total, and it's accurate. So next when the initial SN is then taken out of the chamber, I'd like it to only count the "IN" (1) values between those two serial numbers, and then continue to do the same thing for each following SN that gets taken out. So in excel I’d get this number as follows.

tfitzgerald_0-1584459452409.jpeg

 

I look at the first unit placed into the chamber during the time period I’m looking at and note the serial number. I then have it search for that matching serial number when it’s being taken “out” of the chamber.

tfitzgerald_1-1584459452419.jpeg

 

 

At this point, I set up the formula =COUNTIF(F4:F190,"IN"). In that far right column, and as it goes down it becomes F5:F191, etc. and counts the “IN” within the two SN values to keep a running total of what is in the chamber during that time period.

How do I do this in Alteryx?

 

I feel like next I need to use the multi-row formula tool, but I wont know how many rows I need to have it set to, as this can change per day, is there a way to have it look forward and indefinite number of rows until it find the match? 

 

This is where I am at with getting an output so far, which I feel like is about half way to where I need to be. RunTot_Action is the column I need to keep the running total in. I'm only a couple weeks into using Alteryx and can't seem to figure out the next step of this problem. Thank you all so much for your time in reading this and any help that will get me going in the right direction. 

Data2.jpg

 

Highlighted
Alteryx
Alteryx

I took an iterative macro approach although there may be an easier way. Zipped workflow is attached. 

In before out iteration.png

iterative.png

Labels