How to Filter on the Leading Digit of a Numeric Value that is 4 Digits in Length

[codybentley]

I have a numeric field representing store number that I would like to filter on. The store numbers are either 3 digits or 4 digits long.  I would like to filter on any store number that starts with an 8 and is also 4 digits long.  I've tried the following if statement, but for some reason it returns null values for anything meeting the criteria I'm trying to specify:

if tonumber(Left(tostring([Site Formatted]),1) = "8")
&& (tonumber(Length(tostring([Site Formatted]) > 3)))
then .079
else [UT Total Rate] endif

My questions are, can someone tell me why this returns null values?  Secondly, what is a simpler way of doing this?  And thirdly, can I filter on a numeric value by using the wildcard "?"?  Or is the ? wildcard only meant for strings?  I initially tried filtering on all numeric values that started with an 8 and were 4 digits long by using 8??? but this didn't work either.  Does a wildcard exist for numeric values?

Thank you.

[atcodedog05 · Accepted solution]

Hi @codybentley 

Most of you formula is right. Try this

if Left(tostring([Site Formatted]),1) = "8"
and Length(tostring([Site Formatted])) > 3
then 0.079
else [UT Total Rate] endif

Hope this helps : )

[atcodedog05]

Hi @codybentley ,

Here is a workflow for the task.

Output : 

Workflow has been attached

Hope this helps : )

If this post helps you please mark it as solution. And give a like if you dont mind😀👍

[atcodedog05]

And answer to your last question @codybentley 

Does a wildcard exist for numeric values?

Ans : No, it does not. Wildcard/Regex is only for strings.

[atcodedog05]

Happy to help 🙂

Cheers and Happy Analysing 😀

[codybentley]

Thanks @atcodedog05!