Split rows after running sum

stefankremer

Hi, can someone please help me: I have no idea to realize it.

I want to have a new generated row and a re-clustering (gategory) for the following case

start point:

id company currency amount running sum category

1 A GBP -500 -500 A

2 B GBP 600 100 A

possible results;

id company currency amount running sum category

1 A GBP -500 -500 B (former A)

2a B GBP 500 0 B (new generated)

2 b B GBP 100 100 A

id company currency amount running sum category

1 A GBP -20 -20 A

2 B GBP 50 30 A

3 C GBP 150 180 A

id company currency amount running sum category

1 A GBP -20 -20 B

2a B GBP 20 0 B

2b B GBP 30 30 A

3 C GBP 150 180 A

Categories can be allocated later like for column "category": rename to B if you find running sum 0 and if so then row -1 will be cagtegory B as well. But how to handle this?

Thank you very much for any ideas !!

Alteryx Editions

Accepted answers

All comments

abacon

@stefankremer Not really sure what you are trying to do here in practice, but there is a running total tool you can use. Then once you get the running total, you can do what you mentioned with the mutli row tool and re-cast the category field.

Bacon

Split_Rows_Running_Sum.yxmd

stefankremer

First of all: Thank you for your help.

Please find further explanation for attached example file:

I have rows that show positive and negative values within one category — in this case, GBP cash flows, initally allocated to category A. First, the data should be sorted so that the negative values are summed up (using a “running sum”) before the positive values are added, or vice versa.

The summation should continue as long as possible until the “running sum” reaches zero — that is, until the negative values (or positive values) are maximally offset by the positive values (or negative values), leaving only one direction (either positive or negative) remaining.

See example.xlsx (“Start” table example).

Whenever the “running sum” decreases and can offset some values, a new neutralizing row should be created that brings the “running sum” to zero. This new row represents the neutralization point.

After that, another new row should also be created that reflects the remaining value 5b, 39.500 (“former row ID 5 - 150.000”) from the dataset that contributed to achieving the neutrality (“running sum = 0”).

At the end the categories of all neutralized rows changes from A to B, only 5b stays with category A.

See example.xlsx (“Result Alteryx Workflow”).

example.xlsx

binu_acs

@stefankremer , sample workflow attached based on your provided input , you may need to adjust the workflow make it more dynamic

RunningTotal_22102025.yxmd

stefankremer

First of all: WOW!! And Thank you very much, I'm so happy you @binu_acs found a solution and understand my case. Please find attached the full case and the needed dynamic. Can you help me with as well or is this to complexe for Alteryx or do I need to split table with the criterias?

stefankremer

Thank you so much for answering @abacon . You helped me to define the case more precisely!

binu_acs

@stefankremer Would you mind explaining the logic for each rows or groups in more detailed way for better understanding? Also can you provide expected output based on the given input file ?

stefankremer

Hi, thank you @binu_acs ! Here comes the case more precisely and I attached target table with the source table and expected prep:

Different companies Company A, Company B, Company C have different currency pairs GBP/EUR, USD/EUR. Within each individual currency pair, there are three groups: CF Ordinary, CF Invest, CF Finance. These different groups, in turn, have two variations: intercompany ("1") and external ("#"). Now, within a company, within the available currency pairs, per company, depending on the group (CF Ordinary, CF Invest, CF Finance), the external ('#') cash-flows should simply be netted. The last line within the now offset buckets gets the variation "Y", all others within a bucket then get "N". In the end, a filter will be applied to "Y", and this will identify only the balances per company, per currency pair, per group, depending on external or internal, as a bucket (highlighted in green in the Excel sheet). The purpose of this is that in the intercompany area, only the part that cannot be offset will be passed as the balance.

Within the intercompany cash-flows ("1"), a running sum should then also be formed per company, per currency pair for a company, depending on the group within a company. However, this will follow the already developed workflow, which "fills" the values until the running sum reaches zero, and then a new data record is created for the remainder. The offset intercompany cash-flows ("1") up to zero will then receive a new variation B. The remaining currency from the split entry will then receive the new variation A, just like all other external cash-flows within a company, depending on the currency pair, and depending on the cash-flow group (CF Ordinary, CF Invest, CF Finance).

I am attaching a new file, containing source and target table. In table "target" I attached the column "logic for being a own bucket", I have tried to clarify the bucket definition, I hope you can follow me :-) Many thanks in advance for any kind of help or tips!

more dynamic_source and target table.xlsx

binu_acs

@stefankremer , attaching the workflow, can you go through the output and let me know what the records are which are not expected in this result? Once you provide your validation, I can update the workflow according to that

Sample Data Workflow.yxzp

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703