Reverse the amount by comparing 5 rows above and below

[ferha_jafri]

Hello Experts,

Not sure how to achieve this, I have a scenario in which I have to compare the rows income value for which Code is "ABC-" and Comment is "Int" and then these rows have to compared with above and below 5 rows to check these rows summation whether 1,2,3,4,5 any no. of rows comes out to be equal = 0. In the excel attached , take eg of 200 Income whose summation comes for 1+2+4 row as -200 so it will reverse this amount and this row will not be considered.

Thanks in advance. 

[ferha_jafri]

If it is not possible through pure alteryx tool , then is it achievable through R, Python or macros. I saw many posts in which the sum of all rows equivalent to a single value, but no response till now on the solution for this. Is is not feasible in alteryx? 

Experts can anyone check this and in case if it is not possible to find a solution can anyone confirm so that I can inform the client that on community nobody is able to provide a solution for this.

[Kurohits]

HI @ferha_jafri, 

I guess this could be possible in Alteryx. If I have understood your question correctly, then you are looking for rows offsetting each other or balancing to 5 above or below rows & these rows should be drop off. Rows which are not balancing or offsetting, are to be considered. Correct me if I am wrong. 

If you are looking for something like this, then I have few ideas which you can try: 

1) Identify the unique rows (There should be unique key which identifies each row) 

2) Create a another new column using formula tool and apply a logic to sum 5 rows up and down. 

3) Make another column "Variance" and subtract this column from the column created in previous step. You will get the variance as "ZERO" (0) for rows offsetting each other. 

I am not 100% sure that if this solution is feasible for your problem statement. I will have to dig deep in case this won't works. 

Thanks

[ferha_jafri]

Hello , I am new to alteryx so not able to understand as how a new column when added to previous column result to 0, as in this case the combination of rows can be 1+2 or 1+3.... and so on , it is not like summation of 5 rows only needs to be consider

I have a scenario in which I have to take those rows who has Code as "ABC- " comment as "Int" in the final output, then compare these rows income value for which Code is "ABC-" and Comment is "Int" with the above and below 5 rows i.e. total 10 rows, to check these rows summation for same ID come to 0, rows can be 1,2,3,4,5 any no. of rows . In the excel attached , take eg of 200 Income whose summation comes for 1+2+4 row as -200 so it will reverse this amount and this row will not be considered in the final output. Attaching the sample again.