offset the -ve value from the first positive value

Question

Hello everyone

I am working on a workflow where i have to do do calculation on the below data, i need to offset the -ve value from the first positive value for each ID.

Any suggestions please

Input

IDMonthData1March101April-52Jan102Feb122March-53Jan-53Feb103March124Jan104Feb154March-20

Output - This is the expected output after the calculation

IDMonthData1March-52Jan52Feb123Feb53March124Feb5

Thank you in advance

Ekta · Answer

Hi @mceleavey  , Thanks so much for your reply and the workflow and yes you are right , my first value in output is wrong it should be 5 not -5 😞

The workflow is working accurately on the dataset i provided but when i tried it on my full data , i am not getting accurate output:( don't know what i am missing.

basically what i want to achieve is -

if my input is this

IDMonthData5765584459-1051028511-4512-13

Output i am getting is (which is incorrect)

IDMonthData5752584451018

my required output is - sum of all the negative numbers (-10,-4,-13), we subtract from 1st month , in this case  65-27 = 38 and rest all rows remains same.

IDMonthData573858445905102851105120

Please help me with this, Thank you very much in advance

Ekta · Answer

Hi @TheOC , Thanks so much for your reply, apologies for not being very clear in asking question.

basically what i want to achieve is -

if my input is this

IDMonthData5765584459-1051028511-4512-13

my required output is - sum of all the negative numbers (-10,-4,-13), we subtract from 1st month , in this case  65-27 = 38 and rest all rows remains same.

IDMonthData573858445905102851105120

but in this case - since the first month value is less than sum of negatives 25 < 27

IDMonthData5725584459-1051028511-4512-13

Output is - means  sum of all the negative numbers (-10,-4,-13  =  -27), we subtract from 1st month and left over one from second month , here 25-25 = 0 , and then 44-2 =42

IDMonthData57058425905102851105120

Thank you in advance

mceleavey · Answer

Hi @Ekta ,

I think you missed a few things out here, as in you don't simply off-set against the first positive value, but you off-set the amount against the first value, then the remainder against the next value, and the remainder against the next value etc. until the there is no negative value left. Once the amount has been subsumed by the negative value then it needs to be removed.

And I assume you got the first value wrong, and you meant 5 not -5, otherwise none of it makes sense.

Anyway, I've attached the workflow. Basically I used a sum and join to associate the negative value back to the appropriate IDs, then a multi-row formula to assign the negative balance.

Hope this helps,

M.

mceleavey_solution.yxmd