Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

offset the -ve value from the first positive value

Ekta
8 - Asteroid

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

IDMonthData
1March10
1April-5
2Jan10
2Feb12
2March-5
3Jan-5
3Feb10
3March12
4Jan10
4Feb15
4March-20

 

Output - This is the expected output after the calculation

IDMonthData
1March-5
2Jan5
2Feb12
3Feb5
3March12
4Feb5

 

Thank you in advance

5 REPLIES 5
TheOC
13 - Pulsar

hey @Ekta 
Would love to help but I don't think I understand exactly the calculation required. What do you mean by 'offset hte -ve value from the first positive value for each ID'. Unless I'm mistaken, should the first row of output be:

IDMonthData
1March5


Given the input:

IDMonthData
1March10
1April-5

 

my thinking is the first positive number (10), take away the negative value (-5), would be '5', not '-5'.

 

Am i misunderstanding?

 

Cheers,
TheOC

mceleavey
16 - Nebula

@Ekta ,

 

in addition to what @TheOC said, how can you have multiple lines per ID if you're off-setting against the first positive value? There is always only one first...of anything.

 

M.

mceleavey
16 - Nebula

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.

mceleavey_0-1637863084683.png

 

mceleavey_1-1637863127294.png

 

Hope this helps,

 

M.

 

 

Ekta
8 - Asteroid

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 

IDMonthData
5765
5844
59-10
51028
511-4
512-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.

IDMonthData
5738
5844
590
51028
5110
5120

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

IDMonthData
5725
5844
59-10
51028
511-4
512-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 

IDMonthData
570
5842
590
51028
5110
5120

 

Thank you in advance

Ekta
8 - Asteroid

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                                                     

IDMonthData
5765
5844
59-10
51028
511-4
512-13

 

Output i am getting is (which is incorrect)

IDMonthData
5752
5844
51018

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.

 

IDMonthData
5738
5844
590
51028
5110
5120

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

Labels