Contra Workflow

Hello everyone,

I am new to Alteryx and with help from some good members of this community, we come up with the below workflow:

The intention is to have a workflow which will look for positive and negative numbers which net to zero and remove them from the data if certain conditions are met. For example amount in one of the rows is -10000 and in another row it is 10000

These conditions are that both rows will have:

1. the same business unit code

2. the same location code

3. the same department code

4. the same project code

5. the same PO 2

Is there any chance that someone can help fine tune the attached workflow to do what I want it to do?

Your help will be highly appreciated.

Many thanks

Nuha

NC Contra Rules.yxmc

Help

Accepted answers

JReid

@CeesayN try the attached workflow. It takes some time to run, but what it does is 1-1 matches, and then takes the remaining items and feeds them through a batch macro that uses the optimization tool set to maximize the number of records that meet the parameter Amount = 0.00.

The first step of the 1-1 matching is really just to speed up the n-n matching as each iteration of the macro can add significant time.

RecordMatching.yxzp

JReid

@CeesayN

This should work on 19.1, but it does require the predictive package to use the optimization tool.

All comments

benakesh

Hi @CeesayN ,

Please include input data file and sample expected output .

JReid

Will the matches always be 1-1 or could there be 1-n or n-n?

If it's always 1-1 I'd recommend a batch macro, if it's 1-n or n-n then you may want something a bit more powerful.

CeesayN

@JReid

Thanks for looking at my query and apologies for the late reply, I was off ill yesterday. The matches will not always be 1:1, there will be instances where you have more than one row matching the criteria but I want the offset to happen when the first condition is met.

Regards

Nuha

CeesayN

@JReid

Sorry, in response to your specific question, I think it is n-n if I understand you well because you do not always know which numbers will be offset against others.

Regards

Nuha

CeesayN

@benakesh

Please find attached the input file. I am not able to get the expected output without the solution as this will involve a lot of manual work which is what I am trying to avoid at all cost.

The expected output should ensure that where there are positive and negative amounts of the same value in the data, these to be offset where the given criteria is met.

Hope this helps

P&L Output.csv

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703