Hi,
I have multiple files in the format:
When I use the transpose and cross tab tools on a single file I achieve what I need, ie:
However when I use multiple input via the wildcard I get the following:
I need to append the output after the crosstab to all of the input files since each report contains about 20 fields of data all individual to that report.
Thanks!
Solved! Go to Solution.
Hi @TomMishima
I used a batch macro to feed in the different files using a directory tool. Within the batch macro I transposed each dataset to reflect the output you were looking for.
I added in a sample tool within the macro to remove the Report 1 header file as I wasn't sure if you needed this. If you want this you can filter out the first row and join it back to the data after the transpose within the macro.
Workflow attached built in Alteryx Designer 10.6.
Best,
Jordan Barker
Solutions Consultant
Hi @JordanB,
That works perfectly! Thank you so much!
Sorry I just wanted to ask how I adapt your flow so my proper files are used instead?
Hi @TomMishima
In the directory tool you can browse to your folder where your files reside.
I used yxdb files so depending upon your file type the workflow may need adapting. What file type are you using?
Best,
Jordan
Hi Jordan,
I changed the directory in the macro and chose a single file, then in the flow I changed the directory tool to point to my folder and changed to .xlsx. When I run the flow I get an error on every file, 'The FileID does not match in the FileHeader'.
Sorry to be a pain!
The macro runs just fine, it's just the main process.
It also says that the output connect 'Output7' was not valid
I think it's because I didn't save the macro before I ran it. I did this and now it's saying no sheet specified, you must specify a sheet, but I can't see anywhere to enter this.
Sorry for the messages I know this may not make sense. Please do let me know if I can provide further info.
Thanks
Hi @TomMishima
Attached is an xlsx version. We just needed to add the sheet name in the formula tool before the macro. If your sheet names change you can add them in the formula tool before the macro.
The sheet names for the sample files is sheet1
Best,
Jordan Barker
Solutions Consultant
Hi Jordan,
It worked perfectly! Thank you so much for all your help. You are an asset to this community :)