Why does REGEX_Match fail to validate 0.00 in a vwstring or string field in ALteryx

[nagols5445]

Hi everyone,

I'm working on a dataset in Alteryx where the vwstring field contains values in the format 999999999999999.99.  The requirements for validation are as follows:

1. The value must have exactly two digits after the decimal

2. The number before the decimal can be up to 15 digits (leading zeros are allowed in the case where the number is less than +/-1.

3. A negative sign is allowed but optional.

4. Valid examples include point 0.15, 123.45, 0.00, 999999999999999.99, and -123.45.

I created the following REGEX_Match formula to validate the field:

REGEX_Match([FieldName],"^-?((0|[1-9]\d[{0-14})\.\d{2})$")

this works fine for most cases, but when the value is 0.00 the vwstring field seems to automatically convert to zero (0) when it enters the formula tool..As a result, REGEX_Match fails because it is trying to match 0.00 but encounters just zero (0). 

My questions are:

1. Why is the vwstring field being interpreted as 0 instead of 0.00 in the formula tool? I changed the type to string and I got the same result.

2.How can I adjust my formula or workflow to ensure that 0 .00 is correctly validated?

Any insights or suggestions will be a greatly appreciated.  Thanks in advance!

[alexnajm]

Your pattern doesn’t seem correct to me off hand - for example, why are the parentheses there if you are doing a Regex Match? Plus there is a missing closed bracket

highly recommend putting it into Regex101 for testing purposes :) I can try it out later if needed but traveling currently

 

[nagols5445]

Alteryx is on my work computer and I cannot log into community from the work computer, so I am retyping the formula.  Here is the formula run through Regex 101 

REGEX_Match([FieldName],"^-?(0|[1-9]\d{0,14})\.\d{2}$")

I have text input field containing values and I pass it to the formula tool containing the REGEX_Match formula.  We want the values in the file to be evaluated as is.  Appreciate your help.

Test values: 0.15, .15, 123456.789, -292054.86, 54321.10, 0.00, 0, 1.24285E8

[alexnajm · Accepted solution]

I am now a bit unsure of the ask - 0.00 is working fine with the expression in your reply and the majority of your use cases are working correctly for the expected criteria above (i.e. the Match column equaling zero either doesn't match the 2 decimal criteria or the needing of up to 15 digits before the decimal criteria)

Please share your workflow for further help

[nagols5445]

Thank you for your help, the issue was that my list was all numeric so the expression was failing. I will add a row containing a string and then filter it out to ensure the values are recognized as string.  I will accept your solution.