In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

help

shahnawaz_khan
8 - Asteroid

hi All

 

Please help in getting below "Want this" column.

 

 

 

from thisWant this
AB-24-1234-1234-00000000Captured
AB-24-1234-1267-00000000Captured
12-2345-0000012345-18Captured
12-2345-0000067890-18Captured
1234508Not Captured
wewerNot Captured
 Not Captured
11-2345-0000067890-18Not Captured
OC-24-1234-1267-00000000Not Captured
6 REPLIES 6
flying008
15 - Aurora

Hi, @shahnawaz_khan 

 

What is the rule of get ?

shahnawaz_khan
8 - Asteroid

 

if numbers are in below format and in below length (24 & 21) then it will called as "captured" other wise it will consider as "not captured".

 

from thisWant thisLength
AB-24-1234-1234-00000000Captured24
AB-24-1234-1267-00000000Captured24
12-2345-0000012345-18Captured21
12-2345-0000067890-18Captured21

 

 

Not Capture as the details are not in the format or the length of the number is not acurate.

1234508Not Captured7
wewerNot Captured5
 Not Captured0
11-2345-0000067890-18Not Captured21
OC-24-1234-1267-00000000Not Captured24

 

 

ntakeda
12 - Quasar

 

I don't fully understand the rules of "Captured", but this can be achieved using the RegEx tool.

  • \d represents a digit.
  • \w represents a letter or a digit.
  • [A-Z] represents an uppercase letter.
  • {n} specifies the number of characters.
  • If there are multiple patterns, use | to separate them.

For example, if the your pattern consists of:

  • Uppercase letters (2) - Digits (2) - Digits (4) - Digits (4) - Digits (8)
  • OR Digits (2) - Digits (4) - Digits (10) - Digits (2)

The regex would be:

[A-Z]{2}-\d{2}-\d{4}-\d{4}-\d{8}|\d{2}-\d{4}-\d{10}-\d{2}

 

 

2025-03-06_17h34_14.png

 

For details, ask ChatGPT about RegEx, and it will explain.

shahnawaz_khan
8 - Asteroid

Hi,

 

A policy number that starts with "AB-" should have a length of 24 characters, and a policy number that starts with "12-" should have a length of 21 characters.

If a policy number starts with "AB-" or "12-" and has the corresponding length of 24 or 21 characters respectively, then it is considered valid (True). Otherwise, it is considered invalid (False).

 

I have also mention the reason for better under standing



Policy numberLengthoutputReason
AB-24-1234-1234-0000000024TRUEMatched with criteria hence true
AB-24-1234-1267-0000000024TRUEMatched with criteria hence true
12-2345-0000012345-1821TRUEMatched with criteria hence true
12-2345-0000067890-1821TRUEMatched with criteria hence true
12345087FALSENot matching with the criteria hence false
wewer5FALSENot matching with the criteria hence false
 0FALSENot matching with the criteria hence false
11-2345-0000067890-1821FALSENot matching with the criteria hence false
OC-24-1234-1267-0000000024FALSENot matching with the criteria hence false
flying008
15 - Aurora

Hi, @shahnawaz_khan 

 

FYI.

 

 

IIF((StartsWith([Policy number], 'AB') && Length([Policy number]) = 24) or (StartsWith([Policy number], '12') && Length([Policy number]) = 21), 'Captured', 'Not')

 

or

REGEX_Match([Policy number], '^AB-\d{2}-\d{4}-\d{4}-\d{8}$') or REGEX_Match([Policy number], '^12-\d{4}-\d{10}-\d{2}$')

 

Policy numberGet
AB-24-1234-1234-00000000Captured
AB-24-1234-1267-00000000Captured
12-2345-0000012345-18Captured
12-2345-0000067890-18Captured
1234508Not
wewerNot
 Not
11-2345-0000067890-18Not
OC-24-1234-1267-00000000Not

 

shahnawaz_khan
8 - Asteroid

Its working perfectly fine. Thanks

Labels
Top Solution Authors