Joining 2 files to create model scenarios

[Paul_L]

Hi there,

I have 2 sets of input files, a base set of locations and then a second file containing a list of potential locations. 

For example: 

Base Set:

LocationRef     Lat         Long

Location1        12.124   -11.454

Location2        15.431   -13.415

etc

Potential Locations:

LocationRef           Lat         Long

PotentialLoc1        12.124   -11.454

PotentialLoc2        15.431   -13.415

I would like to create a set of scenario input files (and ideally be able to loop through them rather than creating a set of files and reading them back in) that include all the base locations plus each of the potential locations one at a time i.e.

Scenario File 1:

LocationRef          Lat         Long

Location1              12.124   -11.454

Location2              15.431   -13.415

PotentialLoc1        12.124   -11.454

Scenario File 2:

LocationRef            Lat         Long

Location1               12.124   -11.454

Location2               15.431   -13.415

PotentialLoc2         15.431   -13.415

I get the feeling this should be simple (as I could do it in VBA in minutes), but I'm struggling to do this in Alteryx so that I can build it in to a workflow.

Many thanks,

Paul

[pcatterson]

This sounds like a job for batch macros.  If you are not familiar with the concept, there are some great resources on the Alteryx website and within the community already:

https://help.alteryx.com/current/BatchMacro.htm

http://downloads.alteryx.com/Alteryx8.6.2/WebHelp/BatchMacro.htm

https://community.alteryx.com/t5/Engine-Works-Blog/Alteryx-Simple-Batch-Macros/ba-p/2251

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Batch-Macros-An-Example/ta-p/1137

etc.

[Paul_L]

Thanks, I suspected that batch macros might be the answer and I've used them to loop through files before, but not rows in a table, so I'm struggling to know how to start.  

Does anyone have an example workflow for a batch macro that loops through rows in a file - in my case a CSV file.  I could achieve it by separating my scenario file in to multiple individual files containing 1 row each, but that doesn't seem very efficient.

Thanks,

Paul

[AdamR_AYX]

Here is an example that produces your example data

The key concepts are:

1. A batch macro runs as many times as there are input rows to the control parameter input.  So we feed the potential locations data in there.

2. The group by functionality filters the data within that batch run.  So in this example we filter the potential locations data so we only get the potential location for the iteration we are on.

3. A batch macro doesn't have to have a control parameter!

[Paul_L]

Hi Adam, Thanks - unfortunately I'm getting an error message relating to the version number. I'm using 10.6, was your example saved from v11? Would you be able to save a compatible version please? Looking forward to seeing how it's done! Thanks, Paul

[AdamR_AYX · Accepted solution]

Try this one

[Paul_L]

Thanks Adam!