Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

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 #19: Excel Record Locator

GeneR
Alteryx Alumni (Retired)

For this week’s challenge we can look at using Alteryx to automate a repetitive task.   The link to last week’s challenge (challenge #18) is HERE.

Use Case: Customer has 100's of xls files with 1 common sheet available in all workbooks. Through one process, the user would like to read across all of the xls files and return the values contained in specific cells - Row 2, Column 3 and Row 8, Column 2 for each sheet within each XLS workbook.

The result should be a table OR browse tool containing 3 columns: XLS File, Row2_ Column3, and Row8_Column2.

You will only have 2 xls files for this challenge, Book1 and Book2, but keep in mind that the use case is for 100s of Excel files with the same schema.  You won’t want to use 2 input tools since that would not scale to 100’s.  Also, for all data consumption, please check the box for First Row Contains Data.  This is because in the headers for an Excel file are in row #1.

Good luck and keep it simple, this should be an easy challenge!

TaraM
Alteryx Alumni (Retired)

The soultion has been posted above.

Spoiler
2016-04-04 12_21_13-Alteryx Designer x64 - Week19_DataPrep_RecordLocator_Beginner_Solution.yxmd_.png
Tara McCoy
SeanAdams
17 - Castor
17 - Castor

Provided solution from @GeneR and @TaraM is more direct - attached mine below

 

Spoiler
- Added a row-ID using a multi-field formula (same as provided)
- Added a field that only held the value for the Row2 result, and a name column
- Same for the row 8 result, and a name column
- Filtered out all blanks
- Quick transpose
- job done...

Same outcome - just a different approach
MarqueeCrew
20 - Arcturus
20 - Arcturus

Yikes!

 

I missed the K.I.S.S. principle here.

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
NicoleJohnson
ACE Emeritus
ACE Emeritus

We actually used this one as a demo in a User Group meeting last year - just realized I already had the answer! Posted! :)

 

Spoiler
Solution: Tile (to get Record ID by XLS file name) > Transpose > Filter for Column 3/Row 2 (F3 & Tile # 2) and Column 2/Row 8 (F2 & Tile # 8) > Cross Tab
WeeklyChallenge19.JPG
estherb47
15 - Aurora
15 - Aurora

Used a directory tool, dynamic input, and tile tool

Laurap1228
11 - Bolide

My solution was the same as the one provided.

 

Spoiler
challenge19.jpg
LordNeilLord
15 - Aurora

Pretty much the same as the solution

 

Spoiler
Weekly Challenge 19.png
mceleavey
17 - Castor
17 - Castor

Ok, going through the back catalogue.

This was a nice and easy one and I assume everybody has done this in pretty much the same way:

 

Spoiler
I started by setting the path in the input tool to use a * to replace the filename of the Excel sheet, then set the filename to be brought in as a column in the input tool using the "Output Filename as Field" option. Then I used the multi-row formula to assign a Row ID, restarting the numbering sequence at each change in filename.
I then used a formula to determine where it was Row ID 2 and Row ID 8, assigning the column names as the value, and assign a column to highlight these rows, and a value field to return the value of column 3 where the Row Id was 2 and column 2 where the Row ID was 8.
I then simply removed the extraneous rows and cross-tabbed the data accordingly.

Solution.PNG
Results.PNG


Bulien

LordNeilLord
15 - Aurora

Workflow