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

    David Byrne

      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
          Dimitri.B

          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
            Richard Leeke

            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
              Richard Leeke

              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
                Dimitri.B

                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
                  Richard Leeke

                  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
                    Dimitri.B

                    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
                      Richard Leeke

                      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
                        Dimitri.B

                        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