4 Replies Latest reply on Jul 8, 2021 7:44 PM by Christine Choy

    To get the highest digit in a number column

    Christine Choy Active Member

      As title, just wondering if any expert here can guide me. I want to get the highest single digit from a number column. Any of the data transformations can achieve this?

       

      Highest number: 9

      Lowest number: 0

           

       

      Column AExpected Outcome
      553366636
      333111999999
      002283718
      00000000
      11111111
      3245789319

       

      Thank you!

       

       

      Regards,

      Christine

        • 1. Re: To get the highest digit in a number column
          Namratha Dhoolipalla Guru

          Hi Christine,

           

          You an make use of a Rank Transformation to return the largest or smallest numeric value in a column.

          Please refer to the following guide for further details on Rank Transformation: Rank Transformation Overview

           

          Thanks,

          Namratha

          • 2. Re: To get the highest digit in a number column
            Robert Whelan Guru

            I believe the requirement is to identify the highest digit in each numeric value e.g. if 123454321 is passed in, the output would be 5.

            In this instance the Rank Tx would not be suitable. If it's possible to write a regex expression which can be used in the Expression Tx that would be simplest.

            If it can't be achieved with regex, you will need to parse each digit to individual output fields so that they can be compared to identify the highest digit. The Labeller Tx would be a starting point for this option.

            • 3. Re: To get the highest digit in a number column
              Nico Heinze Guru

              Too complicated, there's an easier approach (which is also much faster than any regex can ever be).

               

              Let's assume that those string values always contain nothing but digits (no blank spaces, no minus sign, etc.). In the "worst" case this can be achieved by using a variable port which holds the "cleansed" version of the input value.

              Use the following DECODE():

               

              Decode( TRUE,

                Length( Column_A) > Length( ReplaceChr( 1, Column_A, '9', ''), '9',

                Length( Column_A) > Length( ReplaceChr( 1, Column_A, '8', ''), '8',

                Length( Column_A) > Length( ReplaceChr( 1, Column_A, '7', ''), '7',

                Length( Column_A) > Length( ReplaceChr( 1, Column_A, '6', ''), '6',

                Length( Column_A) > Length( ReplaceChr( 1, Column_A, '5', ''), '5',

                Length( Column_A) > Length( ReplaceChr( 1, Column_A, '4', ''), '4',

                Length( Column_A) > Length( ReplaceChr( 1, Column_A, '3', ''), '3',

                Length( Column_A) > Length( ReplaceChr( 1, Column_A, '2', ''), '2',

                Length( Column_A) > Length( ReplaceChr( 1, Column_A, '1', ''), '1',

                '0')

               

              This works as follows:

              First all '9' digits are removed from the input value. If the result is shorter than the input string, then there is at least one '9' digit, meaning this is the return value.

              Otherwise (no 9 contained) all '8' digits are removed. If the result is shorter than the input string, then there is at least one '8' digit (/but no 9, otherwise the first check would have worked), meaning '8' is the return value.

              And so on.

               

              Regards,

              Nico

              • 4. Re: To get the highest digit in a number column
                Christine Choy Active Member

                Thank you so much for the help, this code works well, performance-wise also pretty fast!