Join columns from 60+ inputs easily

[leon_m_holt]

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-01	2
2018-02	4
2018-03	3
2018-04	2
2018-05	3
2018-06	4

Example of final output;

Month	Indicator 1 Data	Indicator 2 Data	Indicator 3 Data	Indicator 4 Data	Etc...
2018-01	2	0.13	0.86	101	...
2018-02	4	0.22	0.89	112	...
2018-03	3	0.20	0.90	109	...
2018-04	2	0.17	0.81	113	...
2018-05	3	0.11	0.81	102	...
2018-06	4	0.19	0.84	107	...

Grateful for any assistance with this.

Thanks,

Léon

[DavidP]

Hi Leon,

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

[leon_m_holt]

Hi DavidP

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

[danilang · Accepted solution]

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

[WilliamR]

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.

[leon_m_holt · Accepted solution]

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