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