Weekly Challenge

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

We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!

Submit Feedback
We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.

Challenge #180: Thousand File Challenge

Alteryx
Alteryx

A solution to last week's challenge can be found here

 

In celebration of the Data Connections window and many other cool new features in the 2019.3 release, this challenge requires you to read in thousands of files across 15 different file formats: yxdb, sz, avro, csv, shp, gml, sav, json, mif, tab, xlsx, xlsm, qvx, sas7bdat, geo. Each file contains a single value. Report back the sum of all values.

 

Hint:
suffix FileFormat
yxdb 19
sz 16
avro 48
csv 0
shp 12
gml 31
sav 46
json 54
mif 10
tab 14
xlsx 25
xlsm 56
qvx 47
sas7bdat 45
geo 15

Highlighted
15 - Aurora
15 - Aurora
Spoiler
I unzipped the file manually. Then I used a batch macro and edited the xml so that the fileformat = -1 and thus it will read all the files with default settings. Props to @jarrod I believe who showed me that a few years back. It produces an error when trying to read some of the helper files, but it doesn't affect the answer.

Annotation 2019-09-09 124223.jpg

Annotation 2019-09-09 124603.jpg
Highlighted
11 - Bolide

This was fun lunchtime spent.

 

Spoiler
Tip: Creating a Text Input with the numeric FileFormats then joining on the file types from the directory will eliminate all the helper files that some formats require.

First, the macro...

180_file_reader.png

...then the workflow.

180.png

Highlighted
8 - Asteroid

I used the directory tool to bring in the 15 different formats and used the dynamic input to load the specific template for those formats. 1000 files were added but did not match the results. I also tried filtering the records counts and no match.

 

Spoiler
2019-09-09_10-34-00.jpg
Highlighted
Alteryx Certified Partner

A nice quick and i got to use my favourite formula, FileGetExt()

 

Spoiler
Capture2.PNGCapture.PNG
Highlighted
12 - Quasar
Spoiler
Challenge #180 macro.PNGChallenge #180.PNG
Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Challenge Completed

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
Spoiler
Created a macro first:

10-09-_2019_07-59-11.png


I copied the list of extension and file type numbers to a Text Input tool, created a list of files using Directory tool and used Text to Columns tool to separate the file extensions. The macro return 3 fields to include in the sum of values, so I used the formula tool to convert/merge them.
10-09-_2019_08-00-25.png
Result

10-09-_2019_08-00-47.png


Highlighted
Alteryx Certified Partner

You are always so disappointing Dynamic Input Tool.

 

Here's a thought, replace the (not so) dynamic input tool with the same simple batch macro everybody has to create in order to input multiple files. Solution attached...

 

Spoiler
MacroMacroWorkflowWorkflow

 

 

Highlighted
11 - Bolide
11 - Bolide

This one was lots of fun!  I don't have the newest version of Alteryx but still able to complete in reasonable fashion with older tools.