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

Finding alphanumeric words in a cell and setting the value to 1

Abhii2658
8 - Asteroid

I have 70,000 records with various information fields. The requirement is from the Vendor field, if there is an alphanumeric value then set Field A to 1 else to 0

Example 

 

Name                                            Field A 

AB1000 Abhijeet Company AB  -     1

CD2000 Abhijeet Company AB  -    1

EF3000 Abhijeet Company AB -      1

AB1234 Abhijeet Business AB -       1

AB01 Abhijeet Games -                    1

IT2658 Abhijeet Company AB -         1 

123456 Abhijeet Company AB -        0

45 Abhijeet Company -                      0 

 

Hope i am making sense here. Appreciate any advise here. 

 

10 REPLIES 10
atcodedog05
22 - Nova
22 - Nova

Hi @Abhii2658 

 

Can you provide this data in excel.

 

atcodedog05
22 - Nova
22 - Nova

Hi @Abhii2658 

 

You can use formula like this

IF REGEX_Match([Vendor], "\d+") THEN 0 ELSE 1 ENDIF

 

Workflow:

atcodedog05_0-1629205812251.png

 

Hope this helps : )

 

Jean-Balteryx
16 - Nebula
16 - Nebula

Hi @Abhii2658 ,

 

Here is how you can do by extracting your vendor ID then check if it contains only digits. in that case it's false else it's true. I output the result as a Boolean if you need to filter after or anything else but @atcodedog05 solution returns the values you asked for.

 

Capture d’écran 2021-08-17 à 15.22.02.png

HomesickSurfer
12 - Quasar

Hi @atcodedog05 

 

I like it!

messi007
15 - Aurora
15 - Aurora

@Abhii2658,

 

@atcodedog05 nailed it 🙂 below Another way how you can do it as well :

 

messi007_0-1629206835960.png

 

Attached the workflow.

 

Hope this helps !

Regards

Abhii2658
8 - Asteroid

Sorry i dont think i made myself clear, here is the excel 

Vendor Field A 
AB1000 Abhijeet Company AB1
CD2000 Abhijeet Company AB1
EF3000 Abhijeet Company AB1
AB1234 Abhijeet Business AB1
AB01 Abhijeet Games1
IT2658 Abhijeet Company AB 1
123456 Abhijeet Company AB1
45 Abhijeet Company 0

 

Since the Vendor field has an alphanumeric word "AB1000 " FIELD is 1 and so forth 

atcodedog05
22 - Nova
22 - Nova

Hi @Abhii2658 

 

Here is how you can do it.

Workflow:

atcodedog05_0-1629209151986.png

 

Hope this helps : )

 

Maskell_Rascal
13 - Pulsar

Hi @Abhii2658 

 

Here is one more solution that combines both @messi007 and @atcodedog05's solutions. This one should cover you in scenarios where a vendor is listed as just alpha characters, and in scenarios where they are listed as numeric but with any special characters like dashes or periods.

 

IF REGEX_CountMatches([Vendor], '[a-z]')>0 AND REGEX_CountMatches([Vendor], '[0-9]')>0 THEN 1 ELSE 0 ENDIF

Maskell_Rascal_0-1629209288971.png

 

Attached is the workflow. 

 

Cheers!

Phil

Jean-Balteryx
16 - Nebula
16 - Nebula

@Abhii2658 that"s how I treated it but @atcodedog05's solution uses one less tool !

Labels
Top Solution Authors