conditional statement or offset? Help please

[coryanthony]

Hello,

Is there a way to omit offsetting rows? If [Business Area] & [Profit Center] is the same as debit and credit entry, i would like to filter off. 
CREDIT entry of -4,106.68 has the same [Business Area] & [Profit Center] as the DEBIT entry 4,106.68 ; therefore, i would like to filter it off.

Can this be done in Alteryx? Please help. Thank you

DC Amount	LC Currency	LC Amount	Group Currency	Group Currency Amt	Posting Date	Posting Period	Fiscal Year	Doc. Date	Business Area	Profit Center
3,080.01	USD	3,080.01	USD	3,080.01	01/01/2023	04	2023	12/15/2022	8272	18200007
-4,106.68	USD	-4,106.68	USD	-4,106.68	01/15/2023	04	2023	01/13/2023	8272	18200007
4,106.68	USD	4,106.68	USD	4,106.68	02/01/2023	05	2023	01/13/2023	8272	18200007
-5,133.35	USD	-5,133.35	USD	-5,133.35	02/15/2023	05	2023	02/15/2023	8272	18200007
5,133.35	USD	5,133.35	USD	5,133.35	03/01/2023	06	2023	02/15/2023	8272	18200007
-6,160.02	USD	-6,160.02	USD	-6,160.02	03/15/2023	06	2023	03/15/2023	8272	18200007
-11,293.37	USD	-11,293.37	USD	-11,293.37	08/15/2023	11	2023	08/11/2023	8272	18200007

[rzdodson]

@coryanthony here is one way to do this.

[coryanthony]

Hi rzdodson,

Thank you for your time and response. I am trying to follow your steps and make sense of it. It appears i still have unwanted data. I should end up with 3 different entries but you currently have 5 entries. The below should be filter off since they offset(creating a new tab/sheet with these if possible). 

-4,106.68	USD	-4,106.68	USD	-4,106.68	01/15/2023	04	2023	01/13/2023	8272	18200007
4,106.68	USD	4,106.68	USD	4,106.68	02/01/2023	05	2023	01/13/2023	8272	18200007
-5,133.35	USD	-5,133.35	USD	-5,133.35	02/15/2023	05	2023	02/15/2023	8272	18200007
5,133.35	USD	5,133.35	USD	5,133.35	03/01/2023	06	2023	02/15/2023	8272	18200007

The remaining entries should be below. 

DC Amount	LC Currency	LC Amount	Group Currency	Group Currency Amt	Posting Date	Posting Period	Fiscal Year	Doc. Date	Business Area	Profit Center
3,080.01	USD	3,080.01	USD	3,080.01	01/01/2023	04	2023	12/15/2022	8272	18200007
-6,160.02	USD	-6,160.02	USD	-6,160.02	03/15/2023	06	2023	03/15/2023	8272	18200007
-11,293.37	USD	-11,293.37	USD	-11,293.37	08/15/2023	11	2023	08/11/2023	8272	18200007

[rzdodson]

@coryanthony apologies. I had thought you had wanted to just remove the offset row.

If I understand the above now, this should help you out. I also went ahead and collapsed the various tools in a macro for you. 

Hope this helps! :)

[coryanthony]

you are amazing. one issue I came across. It should only offset if the [Business Area] or and [Profit Center] is this same. Please see records below. The amount appears to be related but the [Business Area] / [Profit Center] is different. Therefore, records should be displayed since theyre not offsetting/related. 

DC Amount	LC Currency	LC Amount	Group Currency	Group Currency Amt	Posting Date	Posting Period	Fiscal Year	Doc. Date	Business Area	Profit Center
-11,293.37	USD	-11,293.37	USD	-11,293.37	08/15/2023	11	2023	08/11/2023	8272	18200007
11,293.37	USD	11,293.37	USD	11,293.37	08/15/2023	11	2023	08/11/2023	8279	18200078

[CoG]

Will the offsets always neighbor each other? If yes, I have modified rzdodson's code to accomplish what you requested.

[coryanthony]

Hi Andrew, 

Thank you for joining and the solid question.

 It would not always neighbor each other. Another reason why this code/formula wouldn't work. 

[coryanthony]

Hey Andrew, the solution you provided looks identical to rzdodson with no edits. :)

[flying008 · Accepted solution]

Hi, @coryanthony 

FYI.

1- Group by [Business Area] and [Profit Center] .

2- Expression:

IIF(ABS([DC Amount]) = ABS([Row-1:DC Amount]) || ABS([DC Amount]) = ABS([Row+1:DC Amount]), 0, 1)

3- Filter 0.

Input
DC Amount	LC Currency	LC Amount	Group Currency	Group Currency Amt	Posting Date	Posting Period	Fiscal Year	Doc. Date	Business Area	Profit Center
3080.01	USD	3080.01	USD	3080.01	01/01/2023	4	2023	12/15/2022	8272	18200007
-4106.68	USD	-4106.68	USD	-4106.68	01/15/2023	4	2023	01/13/2023	8272	18200007
4106.68	USD	4106.68	USD	4106.68	02/01/2023	5	2023	01/13/2023	8272	18200007
-5133.35	USD	-5133.35	USD	-5133.35	02/15/2023	5	2023	02/15/2023	8272	18200007
5133.35	USD	5133.35	USD	5133.35	03/01/2023	6	2023	02/15/2023	8272	18200007
-6160.02	USD	-6160.02	USD	-6160.02	03/15/2023	6	2023	03/15/2023	8272	18200007
-11293.37	USD	-11293.37	USD	-11293.37	08/15/2023	11	2023	08/11/2023	8272	18200007
11293.37	USD	11293.37	USD	11293.37	08/15/2023	11	2023	08/11/2023	8279	18200008
Output
DC Amount	LC Currency	LC Amount	Group Currency	Group Currency Amt	Posting Date	Posting Period	Fiscal Year	Doc. Date	Business Area	Profit Center	ID
3080.01	USD	3080.01	USD	3080.01	01/01/2023	4	2023	12/15/2022	8272	18200007	1
-6160.02	USD	-6160.02	USD	-6160.02	03/15/2023	6	2023	03/15/2023	8272	18200007	1
-11293.37	USD	-11293.37	USD	-11293.37	08/15/2023	11	2023	08/11/2023	8272	18200007	1
11293.37	USD	11293.37	USD	11293.37	08/15/2023	11	2023	08/11/2023	8279	18200008	1

[coryanthony]

Well aren't you just amazing. This is brilliant.

One question: What happens the the debit does not neighbor the credit? If the offsetting amounts are not following each other, would this still work?

See input below. Thanks.

DC Amount	LC Currency	LC Amount	Group Currency	Group Currency Amt	Posting Date	Posting Period	Fiscal Year	Doc. Date	Business Area	Profit Center
3080.01	USD	3080.01	USD	3080.01	1/1/2023	4	2023	12/15/2022	8272	18200007
-4106.68	USD	-4106.68	USD	-4106.68	1/15/2023	4	2023	1/13/2023	8272	18200007
5133.35	USD	5133.35	USD	5133.35	2/1/2023	6	2023	2/15/2023	8272	18200007
4106.68	USD	4106.68	USD	4106.68	2/1/2023	5	2023	2/28/2023	8272	18200007
-6160.02	USD	-6160.02	USD	-6160.02	3/15/2023	6	2023	3/15/2023	8272	18200007
-11293.37	USD	-11293.37	USD	-11293.37	8/15/2023	11	2023	8/11/2023	8272	18200007
11293.37	USD	11293.37	USD	11293.37	8/15/2023	11	2023	8/11/2023	8279	18200008
-5133.35	USD	-5133.35	USD	-5133.35	8/15/2023	5	2023	2/15/2023	8272	18200007