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 AmountLC CurrencyLC AmountGroup CurrencyGroup Currency AmtPosting DatePosting PeriodFiscal YearDoc. DateBusiness AreaProfit Center3,080.01USD3,080.01USD3,080.0101/01/202304202312/15/2022827218200007-4,106.68USD-4,106.68USD-4,106.6801/15/202304202301/13/20238272182000074,106.68USD4,106.68USD4,106.6802/01/202305202301/13/2023827218200007-5,133.35USD-5,133.35USD-5,133.3502/15/202305202302/15/20238272182000075,133.35USD5,133.35USD5,133.3503/01/202306202302/15/2023827218200007-6,160.02USD-6,160.02USD-6,160.0203/15/202306202303/15/2023827218200007-11,293.37USD-11,293.37USD-11,293.3708/15/202311202308/11/2023827218200007 Sample.JPG

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 AmountLC CurrencyLC AmountGroup CurrencyGroup Currency AmtPosting DatePosting PeriodFiscal YearDoc. DateBusiness AreaProfit Center 3080.01USD3080.01USD3080.0101/01/20234202312/15/2022827218200007 -4106.68USD-4106.68USD-4106.6801/15/20234202301/13/2023827218200007 4106.68USD4106.68USD4106.6802/01/20235202301/13/2023827218200007 -5133.35USD-5133.35USD-5133.3502/15/20235202302/15/2023827218200007 5133.35USD5133.35USD5133.3503/01/20236202302/15/2023827218200007 -6160.02USD-6160.02USD-6160.0203/15/20236202303/15/2023827218200007 -11293.37USD-11293.37USD-11293.3708/15/202311202308/11/2023827218200007 11293.37USD11293.37USD11293.3708/15/202311202308/11/2023827918200008 Output DC AmountLC CurrencyLC AmountGroup CurrencyGroup Currency AmtPosting DatePosting PeriodFiscal YearDoc. DateBusiness AreaProfit CenterID3080.01USD3080.01USD3080.0101/01/20234202312/15/20228272182000071-6160.02USD-6160.02USD-6160.0203/15/20236202303/15/20238272182000071-11293.37USD-11293.37USD-11293.3708/15/202311202308/11/2023827218200007111293.37USD11293.37USD11293.3708/15/202311202308/11/20238279182000081

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

Sample.JPG

Help

Accepted answers

flying008

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.

录制_2023_11_07_09_32_08_398.gif

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

flying008

Hi, @coryanthony

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

录制_2023_11_07_10_30_15_312.gif

CoG

Here you are! Hopefully, this does everything you would need it to do. The file should be correct this time. If it works, please drop a like on this post!

Solution.yxzp

CoG

That's great! You shouldn't need to update the Macro unless something changes about the underlying data, which would break everything anyway (I'm not sure what else you may be asking with regards to updating the macro).

Here's some a good article on Batch Macro's: https://knowledge.alteryx.com/index/s/article/Getting-Started-with-Batch-Macros-1583461640393

Here's a helpful video as well: https://www.youtube.com/watch?v=NNH_K5U5r78

Trying to explain as simply as possible: A batch macro allows you to perform certain actions on chunks of data instead of running the whole data in one pass. Imagine a pre-check and standard line through airport security (they both get you to your gate, but each line may have different policies and requirements like whether you need to take off your shoes).

In your case, we care less about doing different things for each batch, and more about "batching" the data to begin with. Essentially this case just uses the first half of what a batch macro can do, but that's all you need.

Each batch is comprised of all rows with the same DC Amount, Business Area, and Profit Center. From there, a filter divides the batch into credits and debits, which are each labeled with separate RecordID's and then Union'd back together (the trick is that we can now associate matching credits and debits based on this RecordID)

Example where batch has five records:

Debit RecordID	Credit RecordID
1	1
2	2
3

^ Based on the table above we now have a way to know not to delete Debit RecordID = 3, since no corresponding Credit exists.

Hopefully this was not too confusing, but thorough enough for you to understand your workflow!

CoG

Not to confuse, but I think this is a good learning point (it was for me)! I am also posting a separate solution that does not use a batch macro. Feel free to use whatever you are more comfortable with!

I had a nagging feeling that this could be done more simply, and I finally figured it out, so I wanted to share it with you @coryanthony.

This workflow uses 2 Multi-Row Formulas and output should be identical (I did minor testing on my end).

Non-Macro Solution.yxmd

All comments

rzdodson

@coryanthony here is one way to do this.

Solution.yxzp

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!

Solution.yxzp

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.

Solution.yxzp

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

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.

录制_2023_11_07_09_32_08_398.gif

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

flying008

Hi, @coryanthony

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

录制_2023_11_07_10_30_15_312.gif

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

temppp.JPG

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

CoG

Here you are! Hopefully, this does everything you would need it to do. The file should be correct this time. If it works, please drop a like on this post!

Solution.yxzp

coryanthony

Hey @CoG

I used my excel file with 53k plus entries and amazingly, it appears accurate. I am doing some validating now. I am quite lost on the batch Macro. Not quite sure whats its doing. Do i need to update this Macro?

Action i took - i replaced the sample input text tool with Input data Tool. I at least owe you a coffee

CoG

I had a nagging feeling that this could be done more simply, and I finally figured it out, so I wanted to share it with you @coryanthony.

This workflow uses 2 Multi-Row Formulas and output should be identical (I did minor testing on my end).

Non-Macro Solution.yxmd

flying008

Hi, @coryanthony

Have you fully solved this problem yet? If need, please upload data table both input and output of your want.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703