- Bernard K
TRIM, LTRIM, and RTRIM Functions in Tableau
These functions are used to remove spaces contained in string fields.
The functions can be described as follows.
LTRIM – This function returns a string with any leading space(s) removed.
E.g. LTRIM(“ Tableau Desktop”) = “Tableau Desktop”
RTRIM – This function returns a string with any trailing space(s) removed.
E.g. RTRIM(“Tableau Desktop “) = “Tableau Desktop”
TRIM – This function returns a string with both leading and trailing spaces removed.
E.g. TRIM( “Tableau Desktop” ) = “Tableau Desktop”
These functions come in handy when performing different data prep tasks in Tableau. When combined with other string functions they can be more powerful in accomplishing different tasks such as.
Harmonizing fields before joining or relating data
In this case, I have two sample data sets shown below.
The above tables contain data for same customers in different years. The only difference is some of the customers have been recorded differently on the two tables. There is a mix-up between lower and upper cases, as well as some names have leading and trailing spaces.
Meaning for you to join or related the two tables to conduct any meaningful analysis. You need first to harmonize the fields between the two tables.
To do that, in this case I am going to write a simple join calculation to convert the customer names in Table (2) to Proper case just like in Table (1).
(This simple calculation combines the string function TRIM, and PROPER to convert customer names in Table (2) to proper case, thus enabling joins between the two tables).
Useful: Explore PROPER () function in Tableau
In summary, the above calculation trims any leading or trailing space(s) in the field ‘Customer Name’, before converting the field into proper case. Resulting to a field (virtually) that matches the field ‘Customer Name’ in table (1) – thus enabling joins between the two tables.
If this post was helpful and you would like to receive more of these Tableau tips and tricks, kindly subscribe to our mailing list below.
If you like the work we do and would like to work with us, drop us an email on our Contacts page and we’ll reach out!
Thank you for reading!