topic Re: How to Filter on the Leading Digit of a Numeric Value that is 4 Digits in Length in Alteryx Designer Desktop Discussions

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

codybentley — Fri, 02 Oct 2020 17:53:58 GMT

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.

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

atcodedog05 — Fri, 02 Oct 2020 18:02:18 GMT

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 : )

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

atcodedog05 — Fri, 02 Oct 2020 18:04:08 GMT

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😀👍

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

atcodedog05 — Fri, 02 Oct 2020 18:05:20 GMT

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.

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

atcodedog05 — Fri, 02 Oct 2020 18:19:08 GMT

Happy to help 🙂

Cheers and Happy Analysing 😀

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

codybentley — Fri, 02 Oct 2020 18:21:35 GMT

Thanks @atcodedog05!