Forcing variable input to maintain a given format based on "master data" input

Question

Hello guys,

I am having trouble with the following task I need to fulfill:

My client creates a monthly report and wants to combine a year's worth (12 reports) of them into one output data. The columns and structure are identical but what I should consider is the fact that the data type of some columns can be different (e.g. column B is a Double in report 1 but a Float in report 2). These occurrences can happen at random columns which led me to the idea that I should probably take one of those 12 as the "master data" for reference of the data types, and force the other 11 reports to adapt to the chosen report, so that I can unify them after with ease.

Does anybody happen to have an idea how this could work?

Btw, I would like to make this a standardized workflow so that I can use it for other clients too, meaning there is no need for a reference of the input data, dummy data would work just as fine.

Cela · Answer

@cjaneczko Thank you for the response!

My bad for not specifying what I actually mean by "standardizing" it:

I want the client to be able to use this WF on their own, meaning that will wrap this up as a wizard tool. Since this tool should then be used by various clients, I want them to be able to

1.) select which data types they want for their ultimate output

or

2.) throw in one example input and then let the WF work on adapting the remaining reports based on the first one

This is why I kind of think that a macro might be necessary here for this to work properly, but I am glad to hear any kind of advice on this.

cjaneczko · Answer

See below. In the connection below I have all of my files saved in the same folder as the workflow. So instead of using an absolute path you can use a reference path with the ".\" followed by the part of the file names with a wildcard. In the below instance all of my Excel files were called ClientA-1 and ClientA-2. Since the wild card is put after ClientA, it will bring both -1 and -2 files into the workflow. It would also bring any other files that start with ClientA if they were in that folder. The ClientA-1 file is all Text Fields. My ClientA-2 file is a mix of Text, Currency, Float and Number. Alteryx brings all files in as V_String. After that with a Select tool you can change any of the columns to the field type thats desired.

Cela · Answer

@cjaneczko Thank you for the fast response! Can you elaborate on your solution any further? A screenshot of the WF would be helpful too.

Would pointing the connection to the directory work in a standardized version of the WF as well? I'm asking because my attempt is to make this basically work with wildcards since it should work for any possible client in the future as well.