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

# To get the highest digit in a number column   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  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   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  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   Thank you so much for the help, this code works well, performance-wise also pretty fast!