regex_match

[aastorga]

n one of our workflows there is a validation done in order to check if a column/value it's a numeric value or not.

The expression is working with numbers higher than 0,09. IF values are lower or equal to 0,09 then if fails.

IF REGEX_Match(ToString([XXXXXXX]), "^\d+(\.\d+)?$")

THEN ""

ELSEIF

IsEmpty([XXXXXXX])

THEN ""

ELSEIF

REGEX_Match(ToString([XXXXXXX]),"^\d+(\.\d+)?$")

THEN

"XXXXXXX - Input must be positive values only"

ELSE

"XXXXXXX - Input must be numbers only"

ENDIF

I've tried replacing the expression with "^\d{1,3}(,\d{3})*(\.\d+)?|\d+$" but it didn't work.

Does anyone know how to solve this issue?

Thanks!

[binuacs]

@aastorga @Can you provide some sample data and expected results?

[aastorga]

[binuacs]

@aastorga @Use the below formula 

REGEX_Match(ToString([XXXXXXX]),"[\d,\.]+’)

[aastorga]

I tried but didn't work ☹️

[Luke_C]

Hi @aastorga 

Try this:

IF REGEX_Match(ToString([XXXXXXX]), "[\d,\.]+")

THEN "OK"

ELSEIF

IsEmpty([XXXXXXX])

THEN ""

ELSEIF

REGEX_Match(ToString([XXXXXXX]),"[\d,\.-]+")

THEN

"XXXXXXX - Input must be positive values only"

ELSE

"XXXXXXX - Input must be numbers only"

ENDIF

 

[DS4]

Tried it but didn't work. Seems to be linked with the way Alteryx is transforming some values

[Luke_C]

That value has a letter near the end so I think the logic works.

I agree the scientific notation seems strange. What type of source is the data coming from? 

[DS4]

The input file is an Excel format and the field we want to check is defined as

What we are currently investigating looks like below

IF IsEmpty([Conversion Cost 24]) or IsNull([Conversion Cost 24])
then "Field is empty"
ELSEIF
REGEX_Match(ToString([Conversion Cost 24]), "-?\d\.\d+[Ee][+\-]\d\d?")
THEN "Field OK: scientific notation"
ELSEIF
REGEX_Match(ToString([Conversion Cost 24]),"[\d,\.-]+")
THEN
IF REGEX_Match(ToString([Conversion Cost 24]),"^\d+(\.\d+)?$")
then "Field is OK"
ELSE "Conversion Cost 24 - Input must be positive values only"
endif
ELSE "Must be numerical"
ENDIF

Seems to work but we still need to confirm. Indeed the scientific notation seems strange