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 !!

[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

[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”).

[binu_acs]

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

[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!

[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