community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Join columns from 60+ inputs easily

Meteoroid

Hi, I have been working on multiple workflows which each output two columns of data, the first column is always the month in the year (eg. 2018-01, 2018-02 etc.) and the second is the unique data tied to the workflow (specific indicator). The data in the second columns for each workflow is completely unrelated to each other so need to go down the route of joining based on the first column which I can get to work on a small scale (tested with 5 inputs and 'Join Multiple' tool) but will eventually be looking at 60+ indicators with unique data that will need to be joined and do not fancy the idea of having to add in 60+ 'Input' tools to a workflow.

 

Example of each individual 'indicator' output (from pre-run workflows);

 

Month

Indicator

Data

2018-012
2018-024
2018-033
2018-042
2018-053
2018-064

 

Example of final output;

 

Month

Indicator 1

Data

Indicator 2

Data

Indicator 3

Data

Indicator 4

Data

Etc...

2018-0120.130.86101...
2018-0240.220.89112...
2018-0330.200.90109...
2018-0420.170.81113...
2018-0530.110.81102...
2018-0640.190.84107...

 

Grateful for any assistance with this.

 

Thanks,

 

Léon

Highlighted
Pulsar

Hi Leon,

 

What format is the output from each individual workflow that outputs 2 columns saved as?

Meteoroid
Hi DavidP

The outputs will all be .xlsx for the individual indicators as well as the final output.
Nebula
Nebula

Hi @leon_m_holt

 

Since you are creating the outputs, you can stream them all to one alteryx db with some id having to do with the workflow that it came from, i.e. a column called IndData#.  This 3rd column will be constant for each output stream. All records from workflow 1 will have "Indicator 1 Data" in the IndData# column, workflow 2 will have "Indicator 2 Data", etc.  In your final workflow, you read in the complete data for the db, and crosstab, grouping by the date, with new column headers being IndData# and values coming from [Indicator Data]

 

Dan

Alteryx
Alteryx

Hello,

I have build a workflow with a batch macro which can help you.

The principle is to build a Batch macro doing a transposition of the data to keep the name of the field and to call the macro with the list of file to process, then to use a crosstab to put the field in column.

If it works for you don't hesitate to mark the solution as accepted.

 

Meteoroid

Thanks for the help everyone,

 

Unfortunately, I was unable to view the batch macro suggested by WilliamR but was able to use the logic of the "Cross Tab" tool (I'm not too good with that tool!) suggested by danilang to build a simple workflow. I did not know what was meant by "stream them all to one alteryx db" so improvised using the CReW Macro "Wildcard XLSX Input" to union all the xlsx files from a target folder, which, with my test data, worked a treat! :)

 

Many thanks

 

Léon

 

Alteryx.PNG

Labels