multirow summarize + loop?

[michal100cki]

Hello,

I have an easy issue but anyway I cannot find easy solution. Perhaps you could help me find the best way.

I have a list of sth which are listed in rows. Some of those row values have to be further summarize to the following rows according to mapping available in column (aggregate to row) but that process continues several times ie ->summarized values to row 4-> together with others rows to row 2 then add some more to row 1. I real life it continues a couple more times. In the end I would like to have a list with unique rows and values which were summarized along the way. Example below (left table before, right after). How to create a summarize with a loop?

Thank you!

[JoaoLeiteV · Accepted solution]

Hello there,

It seems that you need to use an Iterative Macro in order to get the results you want. I created a workflow with your input and made a Macro to get to your results.

The Macro I made doesn't have a "Stop" condition as of now and is running 30 loops, it would be best to implement it in case you have too many rows to calculate.

It works as follows:

   - First I summarized the rows and their values just to get the sums.

   - Meanwhile, I used a Unique tool to get the rows and their respective aggregate rows

   - I joined the first two results together to get the Row, The Sum Value, and the Aggregate to row

   - I removed any Null values to avoid getting the wrong data

   - Then, a new Summarize gets the value for the Aggregate to row

   - A Join tool united the new summarized data with the old file, adding the values to each that had none

   - A Select just reorganizes the columns to match the original file and a Union tool stitches everything back together to get the results for that iteration

   - Finally, on the main workflow, I just summarize each row and its final values to match your output.

Could you check if this works for you? If you're having "Null" values at the end, try increasing the iterations inside the macro (View -> Interface Designer, go to last tab an increase the number of iterations) 

[michal100cki]

@JoaoLeiteV 

Great! It looks that it is going to work, I have repeated the proces you presented without macro and it is promising:) Thank you!

Unfortunately, I work on Alteryx 2019.4 and I cannot open your flow directly. Could you upload it in earlier version as I have some issues in setting up iterative macro.  

[JoaoLeiteV]

Hello there,

I only have 2021.2 installed right now. I'll try downloading the same versions as you to save it and then get back to you.

[JoaoLeiteV]

@michal100cki ,

I've changed the main workflow and the macro on Notepad to 2019.4 as seem below:

Could you check if this works on your version? Just remember to put both workflow and macro in the same folder when opening.

[michal100cki]

Hi,

It works. I will verify it in the afternoon.

I didn't know that simple trick will solve it 🙂 But my guess is that it is also due to the fact that previous solution was exported as an Alteryx packed and those aren't so Alteryx can handle them.

Thank you anyway!

[JoaoLeiteV]

Hello,

Glad I could help! Let me know if you have any more questions. 😁