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!

Topic of Interest

Predictive Analysis

Accepted answers

All comments

binu_acs

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

aastorga

binu_acs

@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

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335