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.
Announcement | Get certified today - take the Alteryx Designer Core and Advanced exams on-demand now!

Challenge #180: Thousand File Challenge

Meteor

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

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

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

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
Alteryx Certified Partner

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

 

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

Challenge Completed

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


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
Challenge 180 - Snip.jpgMacroChallenge 180 - Snip2.jpgWorkflow

 

 

Bolide
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.