Cross Tab in a Macro

[rvanderhave]

Hi all,

I have multiple excel files that I want to run through an Alteryx workflow. In order to do this, I use a batch macro to do the necessary transformations per excel file. 

However, I run into a problem. When I create the following workflow for a single excel file:

the Select tool shows the following: 

which is the desired output from this part of the flow. After this part, some more transformations need to be done, which is in this case possible since the fields are known.

When creating the above workflow in a macro, where the input is a table of paths of the different excel files and a correct template file is selected in the Dynamic Input tool:

All the tools have the same settings/selections as in the workflow for the individual excel files, however, when selecting the Select tool, I see the following:

which is, as you can see, not the same as for the individual files. If this would be the final tool in the macro, selecting the unknown fields would be sufficient since this results in the same table as the workflow for the individual files returns. However, as stated above, some more transformations need to be done after this select tool. When plugging in the necessary tools to do these transformations, e.g. a Transpose tool, no fields are shown (which makes sense since the Select does not recognize them as well):

So, all together, my question is: 

What causes the Select tool to not show the fields, even though the same files and settings are used in the tools before the Select tool? And more importantly, how can I solve this?

Thanks in advance for the response!

Kind regards,

Robbert

 EDIT: I see that in the first workflow the First 7 rows are selected, while in the workflow in the macro the First 6 rows are selected. This should be 6 and this does not make a difference to the outcome. 

[LordNeilLord]

Hey @rvanderhave

What is your Dynamic Input tool doing?

EDIT: Sorry I can see you noted it

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[rvanderhave]

Hi @LordNeilLord,

Here you go:

Kind regards,

Robbert

[rvanderhave]

Hi @LordNeilLord or others,

I created a sample dataset and attached it together with the workflow. Hopefully this helps to find a solution!

Kind regards,

Robbert

[LordNeilLord]

Your workflow worked perfectly!

[LordNeilLord · Accepted solution]

Oh and if you were wondering why your macro throws up errors when you test it.... You need to have a dummy file path in your macro input (which is currently blank)... If you put a file path in there... The macro will work on its own

[rvanderhave]

Hi @LordNeilLord,

Thanks for your help! I knew the workflow was working in this way, however, I was unable to do transformations in a later stage of the macro because it would not recognize the columns. Your last comment, adding the file path, solved the issue! I didn't know about that, since all the example macros I have seen did not have a file path in there. 

Again, thanks a lot!

Kind regards,

Robbert

[KrispyBean]

Hi,

I am having a similar problem where I have a crosstab within a batch macro which works fine on its own. But subsequent tools connected to the macro outputs do not retain the columns which change depending on the input. The only difference is that the select (or any other tool) is joined outside of the macro like in the example here.

Not sure if this is related to the same issue.

Thanks