8 Replies Latest reply on Jan 22, 2012 7:57 PM by Dimitri.B

# Calculated Field that converts a Dimension with Decimal Values to Hexadecimal

Looking to create a calculated field that converts a dimenstion called Fault Code which are decimal numbers into hexadecimal numbers. In excel I would do =DEC2HEX(17119,4) and the resultwould be 42DF. Any thoughts on the best way to do this?

• ###### 1. Re: Calculated Field that converts a Dimension with Decimal Values to Hexadecimal

Tableau doesn't have a built-in conversion function (yet), and since it is impossible to do this with a calculated field (because it needs a repetitive  routine), the best way would be to use the data source's built in converter, if it has one.

In Oracle, for example, you can use

`TO_CHAR([decimal_number], 'XXXXXXXXXXXXX')`
by wrapping it into
`RAWSQL_STR( )`
.

Other data sources may have similar functionality.

• ###### 2. Re: Calculated Field that converts a Dimension with Decimal Values to Hexadecimal

Well you can do some really clunky calculated fields - but those will be limited to a given maximum number, because of the fact that you can't call routines, as Dimitri says.

There's an example I came up with for converting in the other direction, plus Joe Mako's simpler version, in this thread.  You should be able to figure out how to do it in the other direction (and if you do, post it here so others can find it).

• ###### 3. Re: Calculated Field that converts a Dimension with Decimal Values to Hexadecimal

Couldn't resist it.

Here's a calc field that handles numbers in the range (0..4,294,967,295) - i.e. up to 8 hex digits:

Define a calculated field [HexDigits] as:

"0123456789ABCDEF"

Then define [HexNumber] as:

IF (([Number]<0) OR ([Number]>=65536^2)) THEN

"Out of Range"

ELSE

MID([HexDigits],(([Number]/(16^7))%16)+1,1) +

MID([HexDigits],(([Number]/(16^6))%16)+1,1) +

MID([HexDigits],(([Number]/(16^5))%16)+1,1) +

MID([HexDigits],(([Number]/(16^4))%16)+1,1) +

MID([HexDigits],(([Number]/(16^3))%16)+1,1) +

MID([HexDigits],(([Number]/(16^2))%16)+1,1) +

MID([HexDigits],(([Number]/(16^1))%16)+1,1) +

MID([HexDigits],(([Number]/(16^0))%16)+1,1)

END

• ###### 4. Re: Calculated Field that converts a Dimension with Decimal Values to Hexadecimal

Looks like Tableau doesn't support the % modulo operator (at least v 7.0 doesn't). So the modified formula is:

```
IF (([Number]<0) OR ([Number]>=65536^2)) THEN
"Out of Range"
ELSE
MID([HexDigits],([Number]/(16^7) - (16 * INT(([Number]/(16^7))/16)))+1,1) +
MID([HexDigits],([Number]/(16^6) - (16 * INT(([Number]/(16^6))/16)))+1,1) +
MID([HexDigits],([Number]/(16^5) - (16 * INT(([Number]/(16^5))/16)))+1,1) +
MID([HexDigits],([Number]/(16^4) - (16 * INT(([Number]/(16^4))/16)))+1,1) +
MID([HexDigits],([Number]/(16^3) - (16 * INT(([Number]/(16^3))/16)))+1,1) +
MID([HexDigits],([Number]/(16^2) - (16 * INT(([Number]/(16^2))/16)))+1,1) +
MID([HexDigits],([Number]/(16^1) - (16 * INT(([Number]/(16^1))/16)))+1,1) +
MID([HexDigits],([Number]/(16^0) - (16 * INT(([Number]/(16^0))/16)))+1,1)
END

```

it produces leading zeroes, but works.

Thank you, Richard - saved for future reference.

• ###### 5. Re: Calculated Field that converts a Dimension with Decimal Values to Hexadecimal

Huh - it should support modulo.  What data source were you using?  I tested it with v7 using JET and it worked, and the help says it's a supported operator - no mention of it only working for some data sources:

% (modulo)

This calculates a numeric remainder. For example, 5% 4 = 1.

• ###### 6. Re: Calculated Field that converts a Dimension with Decimal Values to Hexadecimal

Strange, originally I just pasted data from Excel and it didn't like %. Just now tested with live connection to Excel (Jet?), and it looks like % doesn't work with field names but works fine with literals - see screenshot.

Bizarre...

• ###### 7. Re: Calculated Field that converts a Dimension with Decimal Values to Hexadecimal

OK - it looks as if modulo must be a client-side operator implemented in Tableau - hence the message about being aggregate or constant.  I just tried on an aggregate and looked at the logs and the modulo certainly wasn't sent to the database.

I just had number as a parameter when I tested that expression - which is why it worked for me.

It's not a problem, because if you want the hex string you want individual values anyway, so you need to have rows uniquely identified and just use ATTR() of the number...

• ###### 8. Re: Calculated Field that converts a Dimension with Decimal Values to Hexadecimal

OK, that makes sense, and the modified formula works as advertised.

`IF ((ATTR([Number])<0) OR (ATTR([Number])>=65536^2)) THEN`

"Out of Range"

ELSE

```MID([HexDigits],((ATTR([Number])/(16^7))%16)+1,1) +

MID([HexDigits],((ATTR([Number])/(16^6))%16)+1,1) +
MID([HexDigits],((ATTR([Number])/(16^5))%16)+1,1) +
MID([HexDigits],((ATTR([Number])/(16^4))%16)+1,1) +
MID([HexDigits],((ATTR([Number])/(16^3))%16)+1,1) +
MID([HexDigits],((ATTR([Number])/(16^2))%16)+1,1) +
MID([HexDigits],((ATTR([Number])/(16^1))%16)+1,1) +
MID([HexDigits],((ATTR([Number])/(16^0))%16)+1,1)
```

END