community
cancel
Showing results for 
Search instead for 
Did you mean: 
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Weekly Challenge
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Unable to display your progress at this time. Please try again a little later, or contact an administrator if you continue to see this error.

Challenge #118: Think Like a CSE - A not-so-wild-wildcard input!

Quasar
Spoiler
Multiple solutions here, I suppose, including just asking them to edit the sheet name.

I went with using the crew macros.

Challenge #1178 1.PNG
Meteor

My solution files are attached. This was definitely a challenge for me!

 

Spoiler
In order to import and union data from multiple .xlsx files with different sheet names and field names, the co-worker should do the following:

1. Create a 
batch macro that reads in a template file. The Output Mode in Interface Designer properties needs to be configured to "Auto Configure by Name" to avoid the different schema error.

2. Create a 
standard workflow that reads in the list of the .xlsx filenames.

3. Insert the batch macro from Step 1 into the standard workflow and add an output tool.


The result of the output tool is the data from all the .xlsx files unioned together.

Batch macro
challenge_118_jackie_macro.JPG

Workflow
challenge_118_jackie_workflow.JPG

 

Bolide

So this was an exercise I did with my colleagues about a year ago. Fun challenge! Instead of using the macro I already had, tried a new approach with a filter before the dynamic input, as many others (and this community) suggested. Pretty cool!

Alteryx Certified Partner

I kept my nice and simple

 

A lot of other solutions were over complicating their macros (imho) it doesn't need to be like that

 

Spoiler
118.png

 

 

Alteryx Partner
Spoiler
Capture.PNG
this was an interesting one, I first tried solving it from scratch using the directory input and some standard alteryx logic, but kept thinking that wasn't very elegant, in that pursuit I discovered that the (generally amazing) CReW macro pack already has this solved quite nicely. feels a little bit like aiming a cannon at a bird to use it for just 4 excel sheets, but will certainly start using it in organizations that overuse separate unorganized spreadsheets

Today I learned a thing. That was nice :-) 

Alteryx Certified Partner

Fixes attached. I guess I viewed this as a troubleshooting problem as opposed to an automation problem. In looking at a couple of the macro solutions, neither fixed the formatting issue in File3. The metadata in these solutions showed that Column23 was incorrectly set to string instead of numeric. They also appeared to assume each Excel file would have a single sheet and that the columns present in each file were positionally correct. I think fixing the data sources is the better approach. It was good to see that was the solution in the video. It was interesting to see the use of the Field Info tool and its value in the troubleshooting. I resorted to examining the files directly.

Meteor

Good use of some new tools I hadn't used before.

Meteor

This was definitely a tough one. I had to do some research on the different file names. Great learning on that. Will be very useful in the future!

Spoiler
Workflow.JPGMacro.JPG
Asteroid

It was definitely a challenge for me. It was straightforward coming to think of it but kept getting into "schema error" for the fourth file but finally was  able to crack it just by reading into the finer details.

 

Here is my solution -

 

 

Spoiler

Anyway my approach is simple. Have a directory tool spitting out all the required input files. Connect it to a batch macro. 

At the macro level -
1. Drag an 'Input Tool' and connect to one of the excel file inputs with option for 'List Sheet Names'
2. Have an Dynamic Input Tool that takes the input form the 'Input Tool' above. Here have the tool configured with option 'Modify SQL Query' and set it for 'Replace a specific string' option. 
3. Have a 'Control Parameter' connecting to steps (1) and (2).
4. Connect this back to the 'Macro Output'. You are done. It is at this point you need to go to 'View' -> Interface Designer -> Tools and set the option - 'Auto configure by Name' as shown in image 3 

 

Alteryx Designer x64 - challenge_118_MainFile_vk.pngAlteryx Designer x64 - challenge_118_Macro_vk.pngAlteryx Designer x64 - challenge_118_Macro_InterfaceDesigner_Setting_vk.png

 

Special thanks to @mceleavey for his valuable input regarding 'Interface Designer' within the macro page, never knew such option existed.

 

 

Bolide

Since the schema of the files given are different, we need to use a batch macro tool to complete this task.