topic Re: Contra Workflow in Alteryx Designer Desktop Discussions

Contra Workflow

CeesayN — Tue, 17 Sep 2019 17:06:27 GMT

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

Re: Contra Workflow

benakesh — Tue, 17 Sep 2019 17:31:39 GMT

Hi @CeesayN ,

Please include input data file and sample expected output .

Re: Contra Workflow

JReid — Tue, 17 Sep 2019 18:10:58 GMT

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.

Re: Contra Workflow

CeesayN — Thu, 19 Sep 2019 09:10:13 GMT

@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

Re: Contra Workflow

CeesayN — Thu, 19 Sep 2019 09:14:04 GMT

@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

Re: Contra Workflow

CeesayN — Thu, 19 Sep 2019 10:11:29 GMT

@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

Re: Contra Workflow

JReid — Thu, 19 Sep 2019 13:08:10 GMT

@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.

Re: Contra Workflow

CeesayN — Thu, 19 Sep 2019 16:47:42 GMT

@JReid

Thank you so very much for looking into this for me, really appreciate it. It appears that your version of Alteryx is newer than mine as I am on version 19.1, which prevented me from opening your workflow.

I will try with our IT to update my version to the most recent one 19.2 maybe? and try using the workflow you created.

Regards

Nuha

Re: Contra Workflow

JReid — Thu, 19 Sep 2019 18:43:08 GMT

@CeesayN

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

Re: Contra Workflow

CeesayN — Mon, 23 Sep 2019 09:08:59 GMT

@JReid

Thank you. I am trying to get an upgrade and will let you know when I am able to open the file and use it.

Many thanks once again for your help.

Regards

Nuha