1 of 2 people found this helpful
I guess your calculated field has to be a string, in which case you can just cast your number into string, use IF statement to check its length and tack on required number of "0" to the front. Details depend on what your numbers are and required precision.
Could create a two sets then compare them.
First format your leading zero set to match the length of your other set.
Then create a formatted calc field of the leading zero set. So Right(Right("000000000" + str([number]),9). Then create a set out of that calc field. Call it Set 1 then on create a set on the number dimension and call it Set 2
Then compare the two sets against one another.
[Set 1] <> [Set 2] and put that in your filter shelf and select In
6 of 6 people found this helpful
I faced a similar problem and this is the calculated field that I used. I had to format a Numeric field to standard 11 digit format with leading zeroes.
Applying his suggestion, here's what I did, perhaps not the most elegant solution, but heck, it works:
So I wanted to turn a field with leading zeroes into a string with leading zeroes, in order to match to a file which already defines the field as a string with leading zeroes, specifically in my case, for high school code ("hscode"), which is a 6-digit number. However, the file I was attempting to blend which had student level data was so large that it was in csv format... And when Tableau takes a csv, it makes such a field into a number... Meanwhile files small enough to fit into an xlsx or a sas7bdat file, for examples, can come with predefined field types, but not a csv...
So, I first had to tell Tableau to change the data type for this field to a string. Then, I made a calculated field to convert that, which looks like this:
Calc'd field name: HSCode
//convert [hs code attending] to a 6-character string with leading zeros, as needed
IF LEN([Hs Code Attending])=6 THEN STR([Hs Code Attending])
ELSEIF LEN([Hs Code Attending])=5 THEN '0'+ STR([Hs Code Attending])
ELSEIF LEN([Hs Code Attending])=4 THEN '00'+ STR([Hs Code Attending])
ELSEIF LEN([Hs Code Attending])=3 THEN '000'+ STR([Hs Code Attending])
ELSEIF LEN([Hs Code Attending])=2 THEN '0000'+ STR([Hs Code Attending])
ELSEIF LEN([Hs Code Attending])=1 THEN '00000'+ STR([Hs Code Attending])
4 of 4 people found this helpful
You could have used
RIGHT(STR(000000) + [HS Code Attending],6)
2 of 2 people found this helpful
Another way of doing this (just in case you want to do this on a numeric rather than string field) would be to add an additional 0 to the start of a custom number format.
Right click calculated field > Format > 'Pane' tab > Numbers > Custom > add a leading zero to the start of the expression (and another for the negative half of the custom format syntax).
Charlie's answer is the correct answer. It's both cleaner and requires less computation.
2 of 2 people found this helpful
Yeah, or alternatively, here's how I now go about it, directly in Tableau:
1) Right-click the pill in the left pane (dimensions/measures),
2) Default Properties > Number Format...,
3) Select Custom at the bottom of the list in the left-hand window in the Number Format... pop-up, and lastly, you would simply type in a bunch of zeroes into the custom format text box on the right (i.e., as many zeroes as the amount of digits you want the number to be, e.g., if you want 1569 to still display as 1569, but 1 to display as 0001, then type in four zeroes, otherwise if 1569 should be 01569, then five zeroes, etc., etc.).
While this may have not been an elegant solution, I was able to use it in a Tableau Prep flow where the other solutions are not yet available, e.g. to create a custom number. So, thank you!