Is there a way to filter out integers that are longer than 7 digits?

damen
damen Contributor

Hi all,

So I am working through an ETL and the logic needs to be able to include the bottom two values into a bucket but take the top two out.

I am using a formula that looks something like this:

what it really need to be is:

when `loan number` like '5%' and less than 7 digits then ''

is there a way to program that into the logic? I am unfamiliar with how the syntax would look on that.

TIA

If this helps, feel free to agree, accept or awesome it!

Tagged:

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    @damen If an integer has less than 7 digits, then you could filter to numbers less than 1,000,000 rather than by length (assuming the data type is numerical). So for example:

    when `loan number` like '5%' and `loan number` < 1000000
    

Answers

  • Jones01
    Jones01 Contributor
    edited November 2022

    @damen can you just include AND LENGTH(CONCAT(`LOAN NUMBER`, '')) < 7

  • MichelleH
    MichelleH Coach
    Answer ✓

    @damen If an integer has less than 7 digits, then you could filter to numbers less than 1,000,000 rather than by length (assuming the data type is numerical). So for example:

    when `loan number` like '5%' and `loan number` < 1000000