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!

EddieW
Alteryx
Alteryx

The solution to last week's Challenge can be found here

 

We are thrilled to present another Challenge from our “Think like a CSE” series, brought to you by our fearless team of Customer Support Engineers. Each month, the Customer Support team will ask Community members to “think like a CSE” to try to resolve a case that was inspired by real-life issues encountered by Alteryx users like you! This month, we present the case of using a Wildcard in the Input tool.  

 

Below, we’ve provided the information that was initially available to the Customer Support Engineer who resolved the case. It’s up to you to use this information to put a solution together for yourself.

 

The Case: We have a co-worker who wants to import all (4) files from one of his folders into the workflow to do some analysis. He doesn’t like the idea of having four Input Data tools in his canvas so he decided to give the “Wildcard” input a try. However, when he runs the workflow, only 1 of the 4 files is imported. Our co-worker is not entirely sure why it’s not working.

 

Explanation of the Problem: Four input files (attached to this post) exist in the same directory.  Our co-worker attempts to bring in multiple .xlsx files into a workflow using a “wildcard” in the input tool: input1.png

 

When he runs the workflow, only one of the four files is imported: Hm...not as "wild" as we thought...Hm...not as "wild" as we thought...

 

Then, when he clicks the Input tool, an error appears: error.png

 What's going on?  Can you help our co-worker resolve the case of the Wildcard Input?  Download the four attached files, map your Input Data tool to your file location, and give it a try! 

 

Bonus Question: What do you recommend if our co-worker needs to import 60 different files and using wildcard is giving him all these random errors?  All these files come from a third party.

 

Eddie Wong
Customer Support Engineer
Alteryx
NicoleJohnson
ACE Emeritus
ACE Emeritus

My solution!

 

Spoiler
I love the smell of macros in the morning... :)

Because of the differences in schema and tab name, I chose to go the macro route and feed the directory of file names into a batch macro that would run each one individually and then output the results by column name... now I was a bit lazy, because I believe there would be issues if there was more than one tab per workbook, but for the case provided, this should work for any number of files!
WeeklyChallenge118.PNG

Cheers,

NJ

SuzanneMc
6 - Meteoroid

Did some Alteryx community research ...  different names and file schemes need a one at a time batch macro, fun one! 

patrick_digan
17 - Castor
17 - Castor
Spoiler
Since we only have 1 sheet and it's an xlsx file, I would use a simple batch macro to accomplish the task.
Capture.PNG
ggruccio
ACE Emeritus
ACE Emeritus

Tried several routes!

 

Spoiler
Used a batch macro to get the sheet names dynamically then the same batch macro to get the data.  (It threw me that the sheet names and the schemas were different).

 

trodriguez91
6 - Meteoroid

The client is so close!  However, the input data tool will not read an excel with a tab name that is not consistent with Sheet1.  Therefore, when reading the spreadsheet for File4 we see that an error is raised indicating that the file may be corrupted.  To facilitate the reading of the different spread sheets with different schemas I would recommend using a batch macro!

Spoiler
read_mult_excels.pngbatch_macro_excel.png
mceleavey
17 - Castor
17 - Castor

Nice challenge. I actually had this exact same scenario on site with a client about 8 months ago, so I've already done this. I assume mine is the same method as the others, as a simple batch macro does the trick.

 

Spoiler
I started by identifying the requirement for a dynamic input tool, which would simply be the FullPath field as retrieved via the Directory tool.
I then used a text input tool to create a column called "FullPath" and converted it to a macro input on the right-click menu.
To build the batch macro I simply need to include a control parameter which instructs Alteryx to replace the value in the denoted field with the value being passed in to the macro, and then to simply apply a filter to the "FullPath" field. This will then run through the macro for each value returned. This is then fed into the "Dynamic Input" tool to import each file sequentially:

Batch Macro.PNG

This macro just then needs to be used in the workflow, where a "Directory" tool is used to dynamically determine the "FullPath" field for all files. Remember to configure your batch macro input to map the input fields to the control parameter fields.

Workflow.PNG

THERE IS ONE MORE STEP! 
Don't make my mistake and sit staring at your macro that keeps returning an error regarding the schema...remember to go to your view->Interface Designer page, select the spanner menu (I think wrench, for our American brethren), and select the "Auto configure by name" option.
You're ready to go.



Bulien

GalynaSkamrova
7 - Meteor

Completed with the Community's help. Approach is quite the same as posted.

SeanAdams
17 - Castor
17 - Castor

Simple answer "Mr / Ms Client - please can you fix the sheet name"

 

More comprehensive answer below.

Spoiler

Similar to other folk like @patrick_digan - did a quick batch macro.
2018-06-03_10-10-11.png2018-06-03_10-10-45.png

josefmyers267
5 - Atom

Great challenge. I've never had to do this but after researching the community for solutions this is what I came up with:

 

STEP 1: Make a macro to combine the sheets.

 

 

STEP 2: Reference this macro in your second macro that reads all the sheet paths.

 

STEP 3: Reference the macro in STEP 2 to complete the project. 

 

See attached solution files.