Convert Number to Hex String

Version 7

    Description:

    Tableau's built in functions do not include support for hex representations of numbers.

    This calculation converts a positive integer to a hex string.

    Example Calculation:

    First define a calculated field [HexDigits], as follows:

     

    "0123456789ABCDEF"

     

    Then define a calculation which references this to convert [Number] to a hex string. Two versions of the hex conversion calculation are shown, in order to get around a limitation of the JET engine.

     

    The first version of the calculation works with datasources other than the Microsoft JET engine (text, Excel, Access files). The JET engine gives a "Query Too Complex" error with this version.

     

    This version results in a string dimension.

     

    IF (([Number]<0) OR ([Number]>=2^32)) 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
    

     

    The workaround for the JET limitation is to force the bulk of the processing to happen in Tableau rather than in the data source. This can be achieved by replacing all references to [Number] with ATTR([Number]), as shown below.

    This version results in a string aggregate measure.

    IF (([Number]<0) OR ([Number]>=2^32)) 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
    


    [Number] - a postive integer in the range 0 to 4,294,967,295 (2^32 - 1)

     

    Comments:

    Note that for the second version of the calculation there must be dimensions on the view which uniquely identifies all values of [Number], otherwise the calculation will return '*'.

    The first version of the calculation results in a fairly convoluted SQL expression being sent to the database - hence the error with the JET engine. It is possible that other data sources may also struggle, in which case the second version could be used. Alternatively, many databases have built-in type conversion functions that can be called via RAWSQL.

    At the time of writing the second version of the calculation does not work under Tableau version 8, due to a bug in version 8 beta 8.


    Related Functions:

    Convert Hex String to Number.

     

    Further Reading/Examples:

    This calculation was first developed in this forum thread.