Checking summation for 0 for 5 above and below 5 rows

[ferha_jafri]

Hello Experts I am not able to find a solution for this in normal alteryx discussion so thought to post here,

Not sure how to achieve this, 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. Hope to find a solution as I did not find it in normal alteryx discussion.

Thanks in advance. 

[apathetichell]

I'm very sorry but could you take a bit more time to explain what exactly you are looking for? Take for example your data that you uploaded.

I can tell you which numbers are in columns ABC- and column INT and I can get a sum of any 11 rows (i.e. 5 rows with the same value before and 5 rows with the same value after) are you asking to identify where that sum is zero?  Are you saying any combination of numbers in this group can be zero? How would one allocate this if multiple zero combinations were possible? (say row -3, row -2 and row positive 1 or say row -3, row -1, row 1 and row 4) and how does the present row play into this?

I know you've been working on this project for a few days and looking for a solution - and I think you could get a solution but I know I need need more clarity on exactly what you are looking for and how we get to it.

[ferha_jafri]

The flow is like for same ID we need to check for rows where Code = "ABC-" and Comment = Int, so this row i.e. the current row then will be compare (Rc+R1) = 0 or (Rc+R2) = 0 and so on .......so we will compare Current roa with all rows combination above 5 and below 5 for summation = 0. I can thing about to do it through 100's of if else , in case if you any other approach let me know.

Thanks

[jrgo]

Hi @ferha_jafri 

If I understood you correctly, this may help (not completely solve) with your use case. In referencing the Excel file you shared, all of the rows that you indicated as "Rows to consider" would have been reversed.

However, this approach may not be sufficient with your actual data as this is only comparing a running total SUM of the income in ascending (least to greatest), which may not produce the SUM, not every possible combination of the related records (5 before, 5 after). I'm thinking the best way to do this would be to create an iterative macro that would try each possibilities and stop after a net 0 was found or after all combinations attempted. Unfortunately, easier said than done and I - unfortunately - don't have capacity at the moment to attempt creating this.

[ferha_jafri]

Thanks for your reply, but you are correct it is not working on my data set , the problem which I have identified is the running total, as it is evaluating the cumulative sum so the combination of rows for which the income is reversed is not getting considered. But thanks again as it is very helpful may be helped me in some future project.

    For a solution I created combination of rows but that is the only way how I am getting the correct output. So what I did is took the rows and make 100+ combination of rows as R1+R2, R1+R3.... and so on. The result is coming correct but my only concern with this solution was that I may not miss any combination of rows which should be considered.

[apathetichell]

@ferha_jafriIf you want a dynamic solution you'll either need to brush up on iterative macros and appending columns to iterative macros on runs - or powerset functions in R or Python.

In both cases, I'd run a first macro to append each row to the -5/+5 rows values and then call the the second macro for the python/r/iterative macro for the comparison.  Keep in mind that each entry would generate like 300,000 potential sets so I think it could be very intensive for whatever system you are running.

[ferha_jafri]

Then what would you suggest, I am not good in macros as this is my first project in Alteryx. The manual combination of rows which I generated is working but as I said it is not a feasible solution.

[apathetichell]

O.k - so it's not that Alteryx can't do it per se - more that your project specifications are for the project you are actually undertaking. So as you've explained your problem it's really a statistic/pattern recognition pattern where each row can have hundreds of thousands of additive combos (row 2+row4+row8+row7) for example. I don't know if that's what you want - that's very R/stat heavy.

My hypothesis is that  it's a functional revenue/debit/credit/offset matching situation. If I'm correct - the 5 plus/minus and potential combinations are semi-arbitrary and most entries are offset by a 1 or 2 values. That's all doable even a combination of 3 entries is doable. Once you start expanding beyond that though and with no rules to how the ten entries can be assembled or the quantity it rapidly becomes a huge amount of combos and well in-excess of what you want. Also keep in mind that as presently structured I believe positives and negatives would both be included (so a value could go up and then down to reach the row 0 amount) perhaps you want to filter for only negatives and use a smaller amount of comparison rows?