12 Replies Latest reply on Sep 10, 2018 4:52 AM by mortenbodaugaard.jrgensen

Hi all,

I'm trying to create a calculated field that will display the values from a numerical field using leading zeroes  in order to match the forrat of the same field in a different data dource.  Old standbys (Left/right justification, Excel formatting tricks) don't produce the result.  It seems like a pretty straightforward problem, but I haven't found anything addressing it.  Thanks for any ideas.

Steve

• ###### 1. Re: how do you add leading zeroes in a calculated field?

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.

1 of 1 people found this helpful
• ###### 2. Re: how do you add leading zeroes in a calculated field?

Could create a two sets then compare them.

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

• ###### 3. Re: how do you add leading zeroes in a calculated field?

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.

LEFT("00000000000",11-LEN(STR([Number])))+STR([Number]).

6 of 6 people found this helpful
• ###### 4. Re: how do you add leading zeroes in a calculated field?

Thanks Dimitri!

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])

ELSE '000000'

END

Woohoo!

~Brett

• ###### 5. Re: how do you add leading zeroes in a calculated field?

You could have used
RIGHT(STR(000000) + [HS Code Attending],6)

2 of 2 people found this helpful
• ###### 6. Re: how do you add leading zeroes in a calculated field?

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).

2 of 2 people found this helpful
• ###### 7. Re: how do you add leading zeroes in a calculated field?

Charlie's answer is the correct answer.  It's both cleaner and requires less computation.

• ###### 8. Re: how do you add leading zeroes in a calculated field?

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.).

2 of 2 people found this helpful

• ###### 10. Re: how do you add leading zeroes in a calculated field?

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!

• ###### 11. Re: how do you add leading zeroes in a calculated field?

This is by far the easiest solution!  No nested if statements! No converting data types!

Well done!

• ###### 12. Re: how do you add leading zeroes in a calculated field?

It depends on what output you are expecting. If you are NOT working with a string, but with a numeric field, I would use the formatting approach as mentioned by Brett Chaney or  Charlie Archer