Questions from Tableau Training: Union File Names as Years

Data

Questions from Tableau Training: Union File Names as Years

Our Certified Tableau Trainers are asked a myriad of questions each day. In this series, they share some answers.

There is an example we do in the Tableau Desktop Intermediate course to look at how to union your data. In the example, we use Tableau’s Union function to append different sheets, labeled as different quarters, from a single Excel file into one long table in Tableau. My quick-thinking class the other week immediately wanted to know the limits of Tableau’s ability here. Rodney asked:

“Can we union separate files as the year here?”

Great question, Rodney – especially for a team within a company where there maybe exists a shared folder that uses standardized naming conventions for their files (e.g. Sales 2018). So, I created just a short test of Tableau’s abilities here, with three Excel files labeled as “sales eg (year)” and containing four quarters of data. I also put them in different nested folders so we can make use of Tableau’s Wildcard Union ability.

Three Excel Files

The TL;DR Version in Tableau

  1. Set up Wildcard Union – file names must follow a naming convention
  2. File paths will come in as a new column
  3. Use custom splits and/or calculated fields to narrow the file path field down to the year, e.g. “2018”
  4. Use a calculated field to combine date parts, e.g. [Q]+“ ”+[Yr]
  5. Change the data type to Date
  6. Change the start of your fiscal year from the Default Properties of the pill in the Data pane

 

The Extended Version

To get started, we connect to the first file and set up the union like we would normally. We are looking for files with the stem “sales eg,” with differences occurring in the name after that:

Wildcard Union in Tableau

When we run the Wildcard Union, a new field appears, containing the entire file path:

New Field After Union

So, our steps to complete now are:

  1. Isolate the year from the file path field, and …
  2. Combine with the sheet field to give us a more-complete date field to use.

 

We can do this using custom splits, calculated fields or a combination of the two. The basic functions we will use here for calculated fields are RIGHT and LEFT. Here, I took the right-most nine characters, then the left-most four characters of those:

Year Calculated Field

A simple calculated field can join these fields together:

Calculated Field to Join Fields

Then we can change the data type to Date from the icon in the top-left of the row to get an actual date field:

Change Data Type to Date

Tableau will assign the first month of each quarter to bring the date down to the level of detail it needs: M/D/YYYY in this case. I can change the start of my fiscal year from the Default Properties of my Date field if I need different months associated with each quarter.

Want More Questions from Tableau Training

  1. Questions from Tableau Training: Quick Filter Interaction
  2. Questions from Tableau Training: Labeling with Table Calculations
  3. Questions from Tableau Training: Speeding Up Presentation Views when Using Tableau
  4. Questions from Tableau Training: Dashboard Actions
  5. Questions from Tableau Training: Business Day Calculations
  6. Questions from Tableau Training: Adding Gaps Between Bars
  7. Questions from Tableau Training: When to Pivot Data in Tableau
  8. Questions from Tableau Training: Adding Conditional Formatting
  9. Questions from Tableau Training: Setting Weekday Start
  10. Questions from Tableau Training: How Do ZIP Codes Work in Tableau?
  11. Questions from Tableau Training: The Small Grey Bar Within a Quick Filter
  12. Questions from Tableau Training: Matching Mark Color
  13. Questions from Tableau Training: Showing the Null Indicator
  14. Questions from Tableau Training: Can I Manually Set the Colors of a Stepped Gradient?
  15. Questions from Tableau Training: How Can I Get Box Plot Data into a Crosstab?
  16. Questions from Tableau Training: How Do I Clear a Worksheet? Make a Copy?
  17. Questions from Tableau Training: Opening URL Actions Inside of a Dashboard
  18. Questions from Tableau Training: Can I Move Mark Labels?
  19. Questions from Tableau Training: How Can I Use My Current Report to Create a Viz?
  20. Questions from Tableau Training: Can I Create Text Hyperlinks on a Dashboard?
  21. Questions from Tableau Training: How Can I Transform My Crosstab into a Flat List?
  22. Questions from Tableau Training: Labeling the Top N
  23. Questions from Tableau Training: How Can I Customize Charts with Parameters?
  24. Questions from Tableau Training: Combining Measures into a Single Line
  25. Questions from Tableau Training: How Can I Create Custom Color Palettes?
  26. Questions from Tableau Training: Can a Top N Parameter be Nested Within Other Fields?
  27. Questions from Tableau Training: Conditional Scope on Tableau Calculations
  28. Questions from Tableau Training: Top Ten Plus N
  29. Questions from Tableau Training: Dynamic Label Positioning
  30. Questions from Tableau Training: Using Cross Joins to Solve Active Tickets
  31. Questions from Tableau Training: Top N Highlight Table
  32. Questions from Tableau Training: How to Use Specific Dimensions in Table Calculations
  33. Questions from Tableau Training: Moving Reference Line Labels
  34. Questions from Training: Comparing Metrics for Same Weekday in Previous Year
  35. Questions from Tableau Training: Colouring Specific Columns and Rows in a Crosstab
  36. Questions from Tableau Training: Creating a Custom Legend as a Sheet
  37. Questions from Tableau Training: How Can I Draw a 45-Degree Angle?
  38. Questions from Tableau Training: Down the Aggregation Rabbit Hole
  39. Questions from Tableau Training: Percent Change from Previous Period on a Map
  40. Questions from Tableau Training: Union File Names as Years
  41. Questions from Tableau Training: Conditional Coloring on Sparklines
  42. Questions from Tableau Training: Moving Column Headers to the Top of the View
  43. Questions from Tableau Training: Isolating Rank with Parameters and Calculations

More About the Author

Grant Eisenmenger

Analytics Consultant
Going the Distance in Tableau: Clustering Based on Distance The final example of using distance calculations in Tableau looks at clustering cities based on their relative distance to a selected ...
Going the Distance in Tableau: Dynamically Comparing Different Distribution Centers In this next scenario, we are going to look at which distribution center is closest to a selected city. This is highly similar to the ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072