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

[flying008 · Accepted solution]

Hi, @coryanthony 

The Multi-Row Formula tool depend by position of row, so you need sort the location by date and other at first.

[coryanthony]

okurr. There are levels to this and you're clearly on another level. 

Thank you so much. Much appreciated!

[coryanthony]

Hi @flying008 

one last question. Base on this workflow, what happens when the amounts are the same but does not offset? if both are credits or debits? Please see example below. 

we have 2 debit entries of 3080.01. Since both are debit, it should not offset/filter off. Please note, for all debits [posting code] = 40. For all credits [posting code] = 50. 

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

[coryanthony]

This workflow does not appears accurate ☹️. Data below is being filtered off but should not. These entries do not offset since theyre all credits. 

DC Amount	LC Currency	LC Amount	Group Currency	Group Currency Amt	Posting Date	Posting Period	Fiscal Year	Doc. Date	Business Area	Profit Center	Posting Key
-318.68	USD	-318.68	USD	-318.68	05/01/2023	08	2023	05/01/2023	821G	18200050	50
-318.68	USD	-318.68	USD	-318.68	06/01/2023	09	2023	06/01/2023	821G	18200050	50
-318.68	USD	-318.68	USD	-318.68	07/01/2023	10	2023	07/01/2023	821G	18200050	50
-318.68	USD	-318.68	USD	-318.68	08/01/2023	11	2023	08/01/2023	821G	18200050	50
-384.86	USD	-384.86	USD	-384.86	01/01/2023	04	2023	01/01/2023	821G	18200050	50
-384.86	USD	-384.86	USD	-384.86	02/01/2023	05	2023	02/01/2023	821G	18200050	50
-384.86	USD	-384.86	USD	-384.86	03/01/2023	06	2023	03/01/2023	821G	18200050	50
-384.86	USD	-384.86	USD	-384.86	04/01/2023	07	2023	04/01/2023	821G	18200050	50

[CoG]

It will not work as intended if multiple debits and credits are the same, but not offsetting. The provided workflow will just delete everything that matches Business Area and Profit Center with duplicate absolute value.

[coryanthony]

Hi @CoG  - Thank you again. Because i am not an expert (Far from it), i have a silly question. Can we possible add to the multi-formula? 

ADDING - IF [Posting Code] != [Posting Code] 

IIF(ABS([DC Amount]) = ABS([Row-1:DC Amount]) && [Posting Code] != [Row-1:[Posting Code] || 

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

Posting codes for Debits = 40

Posting codes for Credits = 50

[CoG]

That could help partially, but you still have another problem (even with that excellent idea). Image you have the following table:

DC Amount	Posting Code
5000	40
5000	40
-5000	50
-5000	50

My assumption is that you would want all four rows deleted, but because we only check one row behind or ahead, only the middle two rows would be deleted. I hope this makes sense.

Based on the number of requirements that you have, I'm thinking that a batch macro would be the best bet for you.

[coryanthony]

@CoG 

what an excellent point, solid. Thank you for you time and help. seriously appreciate it. 

I guess i have to figure out batch marco now. But it is possible right? 

[CoG]

I'm trying to build one for you now, but before I finish it, will DC Amount ever be null?

[coryanthony]

@CoG 

 DC Amount should never be null :)