Joining input files

[helen_c]

Hi all - I'm joining a few excel files together that contain survey question data with various participant characteristics and I'm struggling to get it to do what I want. All files contain standard fields (Year, Question number, Measure etc) but they contain different characteristic fields. I'm using the union tool and I want the characteristic fields to line up the totals but instead they contain null values. 

I've attached a dummy workflow - any advice gratefully received!

Helen

[mceleavey]

Hi @helen_c ,

can you provide some dummy data? Or export the data you have to so we can run it?

M.

[helen_c]

Hi - these are the 4 dummy input files. Thanks

[mceleavey · Accepted solution]

Hi @helen_c ,

I've built some tools for this exact purpose. 

The first tool pulls out all the sheet names and the second dynamically loads in all files and unions them together regardless of any variations to the schema.

I've attached the workflow (simply re-point the directory tool to where your files are) and I've attached the macros (you'll need to save them in your macros folder).

I hope this helps.

M.

[helen_c]

Thank you!

[helen_c]

Hi @mceleavey,

I've saved both the macros but they have errors on the 'dynamic input tool' - file not found. 

Also I've repointed the directory tool in the workflow to the correct location but a black question mark icon has appeared after the formula tool with the error message 'the entry point is invalid'

Apologies for the questions - I've not worked with macros much before.

Thanks

[mceleavey]

Hi @helen_c , the black question mark means you haven't saved the macros to your macro folder, or your instance of Alteryx is not picking them up.

As for the file not found, that's fine. It's dynamic, so when you run it it will replace the template file with the file you're picking up.

M.