Data splitting is a handy tool that allows users to partition one column and its data into multiple columns. These columns may contain a string or numerical data with delimiters (e.g. '-', ',' , '/')
Some people will say that this should be carried out as part of "data preparation and cleaning" whether in Alteryx or Prep. However, sometimes you may have a fairly clean data set and you can just jump straight into Tableau and work out what you need to do as you build your viz. Alternatively, you may have forgotten and not realised until you opened Tableau.
Automatic Splitting
This is the most simple and quickest splitting method. When selected, Tableau will automatically split the data into new columns based on a common separator. Whilst also keeping the original column for reference. Below we can see how to carry out this action
Here we can see and break down how Tableau splits this field. Using our example [Order ID: CA-2020-152156] we can see that the order id is split into 3 parts by a dash. Tabluea recognises the dash as a separator and breaks the string down into 3 columns: "CA", "2020" and "152156". From there on we can use these 3 data values seperately in our viz and rename the headers.
Custom Split
This does the same job as the automatic splitting but gives us the abilty to identify and set the delimiter rather than Tableau. This can be useful in instances where the value may have a delimiter that is not recognised as a "common separator". For example if our previous example was [Order ID: CA@2020@152156] we would be able to set '@' as the separator
Here its pertty straight forwards. We can tell Tableau what separator we want to use and then instruct it on how to read the value. Choosing 'First' will read the string from the first value. 'Last' will read the value starting from the end and going right to left. 'All' just uses the whole value.
FInally you can set the number of columns you wish to create. In our example we want 3 as we have three different data extracts. If we add more than whats needed, it will just create an extra empty column
This can also be done on the data pane by right clicking our discrete dimension and choosing Transfrom > Custom Split
Calculated Field
The final way to split our data would be by creating a calcualted field and applying it to our discrete dimension. We can use the Calculation:
TRIM( SPLIT( [FIELD], Delimiter, No. Columns))
We can substitute these values:
TRIM( SPLIT( [Order ID], '-', 1))
Here we are using the 'SPLIT' function to split the identified field ([Order ID]), using the delimiter (-), in ꭕ columns (1).
Tableau will create this column into a new string field, if we want the new fields to be a string then we can encapsulate the whole funtion inside of an 'INT ()' function
These are all great ways to split our data and each has its own advantages and uses. If there are different delimiters in our column we can also split it twice, once by one delimiter and then by the other.