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

[areeba]

Hi @atcodedog05 

The requirement is static, I mean it does not have to adjected dynamically. For example - The conditions does not change run time..

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

On the other hand, I have got more of similar requirements for other combination of Sector, Sector ID and Amount to adjust but that I can implement similarly when above is clear.

Your very initial solution on smaller data was good, apart from the fact that we dont know where the Sector, Sector ID and Amount will appear, I mean in which row we do not know. And that solution was specifically checking at 2 rows up for instance.

Regards,

Areeba

[atcodedog05 · Accepted solution]

Hi @areeba 

Here is a workflow for the task.

Output:

Workflow:

I have added annotations to the tools and added comments where the keys needs to be changed so that you can change for other cases

Hope this helps 🙂

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