Create Dynamic Output Files Based Upon Two Different Fields

[Pantani99]

I have numerous records, each of which contains two separate broker ID fields (broker_from_id and broker_to_id). I need to generate multiple output files, one for each broker, that should include all records where that broker is included in either of those two broker ID fields.

I know that I can use the settings at the bottom of the Output Data tool to produce separate output files based upon the values within a single field, but I'm struggling with how to use two fields.

Thanks in advance.

Example Dataset:

record_id	broker_from_id	broker_to_id
1	BrokerA	BrokerB
2	BrokerA	BrokerC
3	BrokerB	BrokerA
4	BrokerC	BrokerA
5	BrokerA	BrokerC
6	BrokerC	BrokerB

Required Output Files:
BrokerA.xlsx

record_id	broker_from_id	broker_to_id
1	BrokerA	BrokerB
2	BrokerA	BrokerC
3	BrokerB	BrokerA
4	BrokerC	BrokerA
5	BrokerA	BrokerC

BrokerB.xlsx

record_id	broker_from_id	broker_to_id
1	BrokerA	BrokerB
3	BrokerB	BrokerA
6	BrokerC	BrokerB

BrokerC.xlsx

record_id	broker_from_id	broker_to_id
2	BrokerA	BrokerC
4	BrokerC	BrokerA
5	BrokerA	BrokerC
6	BrokerC	BrokerB

[KGT · Accepted solution]

I see the issue here, the option in the Output tool will only output the rows that are there, it will not duplicate the data into multiple sheets, which you need to do. Every record will be output twice in your situation, once for each column.

Easy and simple solution to achieve this will be to duplicate the data and assign the output sheet before union back together and then output using the option at the bottom of the config on the Output Tool.

[Pantani99]

Thank you KGT. That's so much simpler than I had imagined. I was thinking along the lines of an iterative or batch macro lol. It works perfectly.

I've done a lot of VBA coding but I'm learning that Alteryx requires a whole new way of thinking.

[KGT]

Great to hear! Have fun learning! Weekly Challenges are the best tool for working out new methods and then looking at others solutions to see the different ways it's been solved.

When I used to train people, it was obvious that the first 4 palettes could translate, but as soon as you got to the Orange Transform palette, it required a different way of thinking to take advantage of it. The name Alteryx comes from Alter-y,x: Because you are able to transform the data easily and confidently. 

Thinking about data in a workspace and not being afraid to create 3 datasets that you have to bring back together (2 in this case), is a huge change for people. Ordinarily, we would be really concerned about duplicating data and the performance hit, but that's easy to mitigate/track/deal with in Alteryx.