Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Transpose, Crosstab and Append Multiple Individual Files

TomMishima
6 - Meteoroid

Hi,

 

I have multiple files in the format:

 

1.png
When I use the transpose and cross tab tools on a single file I achieve what I need, ie:

 

 3.png

However when I use multiple input via the wildcard I get the following:

 

2.png
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!

11 REPLIES 11
JordanB
Alteryx
Alteryx

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

TomMishima
6 - Meteoroid

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?

JordanB
Alteryx
Alteryx

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

TomMishima
6 - Meteoroid

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!

TomMishima
6 - Meteoroid

The macro runs just fine, it's just the main process.

TomMishima
6 - Meteoroid

It also says that the output connect 'Output7' was not valid

TomMishima
6 - Meteoroid

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

JordanB
Alteryx
Alteryx

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

TomMishima
6 - Meteoroid

Hi Jordan,

 

It worked perfectly! Thank you so much for all your help. You are an asset to this community :)

Labels