Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Weekly Challenges

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

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

kat
12 - 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
jackie_liu
8 - Asteroid

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

 

estherb47
15 - Aurora
15 - Aurora

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!

LordNeilLord
15 - Aurora

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

 

 

dominiklz
8 - Asteroid
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 :-) 

KOBoyle
11 - Bolide

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.

DawnR
8 - Asteroid

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

cplewis90
13 - Pulsar
13 - Pulsar

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
vkarthik21
8 - 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.

 

 

jasperlch
12 - Quasar

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