Tableau Prep and #PreppinData 2024 week 13

An Easter themed #PreppinData for 2024 week 13. Preparing sales of products in the 12 weeks running up to Easter to allow for easy comparison of the period across years in Tableau Desktop.

A nice one step solution this week (see screenshot at the end of this post): a FIXED level of detail calc to get the first sale date per year; then date calcs to get the week, day and day order.

This week marks a quarter of a year learning Tableau Prep. I started with the Tableau getting started guide (2 hours), committed around an hour a week for 13 weekly #PreppinData challenges (a great resource and progression of learning), answered a handful of Tableau Prep questions on the Tableau community forums (5 hours), and blogged about my learnings to reinforce them (5 hours). 25 hours all up and I feel like I’ve got a good grasp of the product and it’s capabilities. It’s a great tool for analysts and those needing to do ad-hoc but often repeatable data preparation, cleaning and transformation prior to analysis. Even better, it’s included with your Tableau Creator license if you’re using Desktop or Cloud as a creator! Definitely give it a go if you use Tableau and have a need to tidy data.

PD 2024 Wk 13

Tableau Prep and #PreppinData 2024 week 12

#PreppinData 2024 week 12, graduate student loan repayment calculator. Good to try out the “value ranges from two fields” option within a “new rows” step. Like some others my interest figure is a little different from the supplied output, however the calc appears to be the same. I also shortcutted the join onto repayment info for undergraduates with a filter (down to just the undergrad row), and joiner fields allowing a simple join on 1=1.

PD 2024 Wk 12

Tableau Prep and #PreppinData 2024 week 11

Week 11 of #PreppinData, and the question: what if there were 13 months in a year? Nice concept to have consistent 28 day months, with 4 weeks per month and each month starting on a Monday and ending on a Sunday. As we found out when we expanded the two row data set though … it’s not as neat as it seems, ending up with a spare day (or two in a leap year).

Part one of my flow answers Jenny’s question “which dates would change months?”. Output gives me 190 as expected.

Part two of the flow looks at Carl’s question of “what the new month(s) would be called”. Turns out the extra month most logically slots in between June and July. This is based on which old month is most associated with a new month number (the number of days of each new month that fall in an old month). And then the “average” of June and July (based on ASCII codes) is Juno. This may (or may not) be Dominican slang for extremely drunk, which coincidentally may (or may not) be what you’d need to be to suggest changing the current calendar in the first place! My preference for the extra day or two at the end is Extrember … as it’s a little extra after December.

PD 2024 Wk 11


Transform field per line data with Tableau Prep (2)

A couple of weeks ago I wrote about a Tableau Prep approach to transposing data from a text file that had a field per line, with another line separating records. At the time I noted that the approach wasn’t robust enough to handle optional fields, and that it would be annoying to need a join per field in cases where you had a large number of fields. In this follow up post I look at an alternative that doesn’t have those drawbacks.

The basic approach is to use a pivot of rows to columns, along with a record number to group fields from the same record. I also introduce a mapping table to allow for less hardcoding of field name and the record separator.

Here is the example source data I have used:

You’ll notice that Hobby and Food are optional fields that don’t appear on every record.

I also have an Excel sheet the defines the record structure:

Here you will see that I map a Field Label (which appears in the source date) to a Field Name that I want in the output. There is also a field called “(end)” which defines the record separator – in this case a blank line.

The Tableau Prep flow is as follows:

This flow:

  • Loads the source and structure files
  • Prepares them to allow them to be joined
  • Joins them together so that we have field names and know the “end of record” lines
  • Calculates a record number by looking for the “end of record” lines
  • Pivots the data from rows to columns
  • And outputs the result

When loading the source data I generate column names (F1, F2) and switch on the extra source row number field (which will be required for the record number calc later):

The “prep to join” steps replace NULLs with empty strings (for the record separator lines) and, for the source data, renames F1 and F2 to Field Label and Value.

The join step is now fairly straightforward, just joining the source data to the structure info on Field Label:

It results in the data like this:

For the pivot step we need something that groups fields for a record together. The “calc record num” step does that, as well as a little tidy up to remove now unnecessary fields and the record separator lines.

The calc for Record Number is using a running sum to work through the source lines in order (ORDERBY [Source Row Number] ASC), generating a number that increments every time we hit an “end of record” line:

 [Source Row Number] ASC:
 IF [Field Name]='(end)' THEN 1 ELSE 0 END

A couple of notes re this calc. It actually makes the “end of record” line part of the next record, but as those lines will be filtered out anyway that doesn’t matter. I also have a follow up calc that adds 1 – it isn’t really necessary but Record Number “0″ looks a bit strange! This is the part that avoids the problem in my previous approach where optional fields weren’t catered for. Previously I was using whole number division to generate a record number from the source row number, and that relied on a set number of fields. The running sum option just looks for the record separators.

Next up, a basic pivot translates the rows to columns:

The pivot requires an aggregation for the values, but as we only have one value per row we can pick either MIN or MAX here. This pivot avoids the join per field that I had in the previous solution.

The output step then outputs the results, in this case:

NB: bold/line added after output.

The new approach successfully handles optional fields, doesn’t require a join per field, and has the added benefit of a mapping file to define structure. One downside is that the field/column order is reversed, but as the resulting CSV is for analysis in Tableau (say) that doesn’t really matter. In my first run I didn’t include the “Food” field; adding that in for a second run. So the solution is reasonably plug and play! You can use the same flow with your own source data and structure definition, you just have to right click the pivot step and “refresh” for it to pick up new field names.

A packaged flow for use in Tableau Prep is available on the Tableau Forums.

One-step Tableau Prep solutions

This quarter I set myself the goal to learn more about Tableau Prep, and a key part of that has been participating in the weekly #PreppinData challenges. Something I’ve noticed, and have been super intrigued about, is that some participants have been posting one-step solutions. That wasn’t surprising during beginner month, but now I’m seeing one-step flows covering reasonably complex multi-step data transformations. Cool!

This week I took a deeper dive into one of those one-step solutions to learn, and share, how they’re being done.

Two quick things first: They say a magician never reveals their secrets, so my apologies in advance to those Tableau Prep magicians who’d rather not see the “magic” shared. Also a hat tip to Hiroaki Morita, who’s week 7 solution I picked as the example to dive into. If I don’t do the techniques justice that’s on me, not Hiroaki!

Right, what do we mean by a “single step” solution? Basically, a single step between the input and output, like so:

For comparison here is my solution to week 7, which has two inputs and then four steps before the output:

The first thing you may notice is that there is only one source: a UNION to pull the two required data sets together. That might seem strange to you as the two tables of data are quite different. I’m going to add a quick “clean” step off the union to take a look at what it’s producing:

What we’re seeing here, boxed in red, is that the two tables are indeed unioned together, and so we get two chunks of data. The first chunk has the columns and rows from the first table of data (the couples and when their relationship started), and the second chunk has the columns and rows from the second table (the gift relevant to each year of a relationship – this challenge was about finding the right gift for each couple based on the length of their relationship).

This union approach seems to be a hallmark of single step solutions; get all of the data into one place/table so that we can operate over it in a single step. It does leave us with a challenge though as it’s not structured or related in the sorts of ways we’re used to, so let’s see how that is dealt with in the single clean step.

There are four sub-steps (or changes) in that clean step:

The first change is to calculate a consistent field across the two sets of data – in this case a “number of valentines days as a couple”. Where Year is NULL (no data) we’ll use the relationship start date [A] Where Year is not NULL we’ll remove the st, nd, rd, th letters from that Year field to just leave a number [B]. In a multi-step solution this would be the consistent field to join the two data sets together.

Because we have all the data in one data set we don’t need to join, instead we need to “look up” the gift from chunk B and plug it into chunk A (where it is missing). And we want to do that based on the consistent field calculated above. So in the screenshot above we can see that we have “number of valentines days as a couple” = 4 on the top row (for “The Loves”) but no gift (it is null). But we also have a row in chunk B with “number of days…” = 4 where we do have the gift (“Fruit/Flowers”).

The next change handles the look up. It uses a FIXED level of detail expression to say “get me the maximum gift from across the whole data set, for this number of valentines days”. Aggregations like MAX will ignore NULLs so we in essence look up the gift from chunk B:

This is potentially another hallmark of one-step solutions then: lookup the value you need from further down the combined data set, rather than using joins.

The fourth change is to filter down to just chunk A, chunk B was only there for the look up after all:

After that the solution simply removes the unecesary columns to be ready to produce the output. Clever, eh!

For me the key points of this one-step solution were:

  1. Get all of the data into one place/table so that we can operate over it in a single step.
  2. Lookup the value you need from further down the combined data set, rather than using joins.
  3. Filter out the data we only pulled in for the lookup.

I hope that you found this as intriguing as I did. And if you’re interested to see more one-step magic keep an eye on the #prep1stepclub X/Twitter hash tag!

UPDATE: Following a really good discussion with another member of the data community I thought I’d add a few notes about why and when you might use a one-step solution. Our conclusion was that one of the strengths of Tableau Prep is it’s clear, easy to understand and maintain, visual layout. Maintainability is a really important consideration, so you may never* use a one-step solution in production, favouring clarity and maintainability instead. However for your own professional development one-step solutions present a useful challenge. They introduce a constraint that forces you to think about problems differently, and in all likelihood use product features that you wouldn’t normally use. That gives you good practice. And afterall, why do we climb hills and mountains that we could otherwise go around?

* Although I say “never” I should point out that I haven’t performance tested common one-step solution patterns against their more natural counterparts. Consequently there may be some benefits (or indeed further drawbacks) that I’m not yet aware of. 

Transform field per line data with Tableau Prep (1)

I recently answered a question on the Tableau Community Forums about transposing data from a text file that had a field per line, with a line of dashes separating records. I’m not sure what the formal name for this format is, but there are similarities with RecFiles.

Here is an example:

I don’t know of a way to use data formated like that directly in Tableau Desktop. But we can use Tableau Prep to transform it into a more natural row per record format!

In this post I’ll cover how I suggested doing that for the forum question. And I plan to follow up with some more generic and robust options in a future post.

First lets take a look at the overall flow given the data above:

What we have here is:

  • An input step (on the left) to load in the file
  • A “clean” step to add a record number to each line
  • Three steps to separate the lines for each field
  • Join steps (Name+Age and Name+Age+Eyes) to join the data set for each field back together to give a traditional record structure
  • An output step to write out as CSV

Diving into each of these components:

The input step defines a split on TAB, headers (F1, F2, etc.), and enables the built in source row number that Prep can add. This row number will be important for identifying a record number next.

The next step adds a record number and removes the dashes which act as a record divider:

Record number is calculated using:

DIV([Source Row Number]-1,4)+1

This is basically just a whole number division (using DIV) of the row number by the number of rows per record, including the divider (4 in this case). Then we filter out the rows with the dashes to get rid of the record dividers. Note that I’ve also neatened up the field names in column F1 above to remove the colon.

Caveat: Because the record number is based on an expected number of fields, this approach won’t be robust enough to handle optional fields that do not appear on some records. This is one reason I’d like to come back and do another post on the topic!

Next we have a clean up step per field to grab just that field and it’s row number, including renaming the column header (F2) to the field name. Here is the step for “Name”: