Lookup for a value greater than each row of a given column and amend it

[Tej_Singh]

Dear Friends,

I have a following scenario to automate in Alteryx. In a given column A all negative values need to be changed to zero after subtracting it for any positive value in the same column A that is greater than the negative value. Example given below. Old Values are the one's I have in a column and New Values is something it should be after amending negative values

Can this be achieved in Allteryx? If so please share a workflow if possible?

Old Values	New Values	Explanation
-5	0
-4	0
-3	0
-2	0
-1	0
0	0
10	5	Since this value is positive and >= than -5 adjusting -5 here
2	0	Since this value is positive and >= than -2 adjusting -2 here
3	0	Since this value is positive and >= than -3 adjusting -3 here
7	3	Since this value is positive and >= than -4 adjusting -4 here
6	5	Since this value is positive and >= than -1 adjusting -1 here
5	5
4	4
3	3
2	2
1	1

[CarliE]

Hi @Tej_Singh ,

Yes! This can be done! Please see attached solution and let me know if you have any questions.

If this helped to solve your problem, please mark as a solution to help other users on the community out!

Thanks!

[Tej_Singh]

@CarliE I am sorry if I wasn't clear your solution is not what I am looking for. Thanks for the reply though.

My requirement is if you have only Old Values column in your Input file, how can we get New Values column data using Alteryx. New Value columns is showing values after adjusting all negative values with positive ones in Old Values.

Can you try that solution with only Old Values column in the input file? 🙂 

[CarliE]

@Tej_Singh,

On line 7 where old value is 10 and new value is 5, how did you know the new value should be 5? Is line 7 looking at row 1 and like 8 looking at row 2? so on so forth?

[Tej_Singh]

@CarliE : I need to adjust all negative values in a given column Old Values with all positive values greater than or equal to the negative value in Col Old Values.

So First I  need to look out for any positive value that is greater than Row 1 value which is -5 in column Old Value. I come across Row 7 value which is 10. Then I will need to now subtract -5 from 10 (10-5=5) and update the reminder at Row 7. So after first look up Row 7 will have value 5 and row 1 (value -5) will be updated to 0 as shown in New Values

Next I will now look up for Ro2 2 value which is now -4 and then follow the above procedure. This has to be done for all negative values in Old Values column such that it exactly matches the output in New Values Column.

Is the problem statement clear now?  Can you suggest how to achieve this?

[Christina_H · Accepted solution]

This works on your given dataset, but will only work as long as there are sufficient positive values greater than your negative values.  It matches the largest negative number with the largest positive number and so on.  The new values don't match yours as they have been matched in a different order.

[Tej_Singh]

@Christina_H Thanks. I will check your workflow and let you know if this solves the purpose.