Date string manipulation and reformation

1. Date transformations and visualizations

Welcome back. In this chapter, we'll be covering how to manipulate date time strings using Tableau's date validation functions before diving into the string manipulation for invalid dates.

2. Splitting up data in Tableau

As you ingest data in Tableau, you'll realize data can be encoded via delimiters that can be split into their own fields. Now Tableau by default, comes equipped with two split options.

3. Splitting up data in Tableau

Split which segments the data based off the most common delimiter, as can be seen from the markdown table to the right.

4. Splitting up data in Tableau

And custom split, which separates the data based off the custom delimiters specified.

5. Calculated splits in Tableau

Assuming you have more than one delimiter to split, you have to specify what the position of your split should be. The position indicates whether the split is performed left to right, or right to left and its important. Looking at our example, we can see that position 3 and -1 are interchangeable with each other. Lastly, like any date transformation function, splits can be combined with other functions such as IF() and TRIM() to further amend textual fields.

6. Validating dates

Now assuming we've got a series of date strings ingested in Tableau, we can come across situations where some time series data isn't in an ISO8601 format. Tableau will automatically categorize this as a string. Rather than identifying this manually, we can make use of Tableau's ISDATE() function to immediately return whether a date is valid or not. However, it's important to note that ISDATE only tests for date formatting. Invalid dates can still exist.

7. To parse or not to parse?

To evaluate dates that have passed the ISVALID test, we can use the DATEPARSE function. The beauty of DATEPARSE is that it can be can be utilized to convert date formats which we believe are valid, but Tableau doesn't recognize these as dates. For example, we might read a date month-year-date and understand this, but Tableau wouldn't, unless we explicity specify the format using DATEPARSE().

8. Cleansing with date truncation

So far, we've covered how to identify invalid date timestamps using ISVALID and DATEPARSE, but we can also make use of DATETRUNC to correct dates for us. As a refresher, DATETRUNC is a useful function that returns a date value at the earliest date partition specified, including the day of interest. For example, if we're interested in truncating the dates to a weekly basis starting on Monday, DATETRUNC will return a set of dates starting at the start of each week, assuming a Monday start. Now DATETRUNC is particularly useful when we have a recurring reporting period, but we've identified incorrect dates that don't match as shown in our example here. We can see the date is set to the 32nd of September that doesn't exist. DATETRUNC corrects this accordingly to the preceding week, 27th of September so we have the correct reporting periods!

9. The dataset

Now in this chapter we'll be studying an Avocado time series data set. Specifically, we'll be looking at exploring the following themes: visual trends using Univariate and Multivariate Analysis Techniques, Seasonal Variations and Pricing Distributions, Trend analysis and percentiles.

10. Let's practice!

Now onto you. Let's practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.