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

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) 

Summarize.yxzp