Adjusting Negative Amounts

[areeba]

Hi,

I have a requirement in whch I need to traverse through all the rows and depending on condtion, I would like to settle amount.

For example, see the input file-

Sector Amount
C71    -10
C72    -20
C73     70
C74     90

So
If Sector =C71 and Amount < 0 Then Amount becomes zero BUT I have to settle this amount in Sector C73, meaning C73 becomes (70-10) = 60
Similarly
If Sector =C72 and Amount < 0 Then Amount becomes zero BUT I have to settle this amount in Sector C74, meaning C74 becomes (90-20) = 70

So the output file should look this this -

Sector Amount
C71    0
C72    0
C73    60
C74    70

The idea is to settle the negatives of C71 in C73 and C72 in C74 so that the total amount remains same(130).

Please find attached input file.

Thanks 

Areeba

[atcodedog05]

Hi 

Here is a workflow for the task.

Input:

Output:

Workflow:

Hope this helps 🙂

PS: the sectors in your problem statement posted and sector in your data is mismatching.

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[areeba]

@atcodedog05 

Hi,

Thank you for reply. I guess I attached the wrong file but you got that right.. Thanks..

I follow your solution, but in our case it is not necessary that C71 will be two rows up than C73, same goes for C74 and C72 combination.

These sectors may appear at different random lines in actual file. Can you suggest something to make this dynamic, meaning the solution should work irrespective of the row number these sector....

[atcodedog05]

Hi @areeba 

Is the sector name fixed C71 , C72, C73, C74 and is there any kind of ID column. If can you give me a much more varying sample data or info on the above i can help you out.

[atcodedog05]

Hi @areeba 

Here is a variation of the workflow

Input:

Output:

Here ID column is used for grouby

Workflow:

You can change the grouby column in sort tools.

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[areeba]

Hi @atcodedog05 

Please see attached the file with more data. 

So the thing is, the combination of Sector, Sector ID and Currency is Unique.

And my requirement is -

If Sector =C73 and Sector ID=1190 and Currency = USD and Amount <0 Then Make Amount = 0 Then settle that amount for 

Sector =C73 and Sector ID=1290 and Currency = USD combination Then Amount becomes 1657389 - 1989 = 1655400

Then the output file should like this (but keep in mind, we do not know where these combination appear, they may appear at different place in actual file) -

[areeba]

Hi @atcodedog05 ,

Thanks for helping me out.

The requirement is not to group by sectors. It is to adjust amount from one combination of sector,sector ID and currency to another if it is negative. I sent you more details in my previous reply... Kindly have a look and let me know.

Regards,

[atcodedog05]

Hi @areeba 

Is there any specific logic how this combination is considered.

How and why is ID=1190 paired with ID=1290

SectorID is different there are more rows with same sector and currency. 

[areeba]

Hi @atcodedog05 ,

It is the combination of Sector, Sector ID and currency which is unique...SO you don't find duplicates on Sector =C73, Sector ID = 1190 and Currency =USD

Actually there is no logic why 1190 is paired with 1290.  It is how business wants to see it.. 

[atcodedog05]

Hi @areeba 

This scenario fixed where These are IDs that needs to be looked up.

Isnt there any variations in the scenario which might be faced.

We can definitely make it work for those 2 IDs