Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Correct Regex Expression

Gamaliel
10 - Fireball

Hello,

 

I am seeking help as to the correct regex expression for this: I want to separate the account code and the account description in to 2 columns. In the pic below, the account codes are in the beginning of the cell. I made a regex expression in the 2nd screenshot below but it only captures the cell with account codes.

 

My problem below is that the account names which have no codes are returned as null. E.g. for uncategorized expenditure, it must be wholly parsed to account description even if the account code column is blank.

 

Thank you.

 

Gamaliel_0-1627472809910.png

 

 

Gamaliel_1-1627472963177.png

 

8 REPLIES 8
bensilv
Alteryx
Alteryx

Hi @Gamaliel,

 

From looking at your issue, I would say a simple way forward is to add a formula on top to say:

IF ISNULL([Account Description]) THEN [Account Name] ELSE [Account Description]

 

Alternatively, you can utilise the zero or more option around the entire group for digits, which is represented by an asterisk. I would test the logic on https://regex101.com/ to see if it highlights the rule correctly.

Jean-Balteryx
16 - Nebula
16 - Nebula

Hi @Gamaliel ,

 

Could you share a sample of your data ? Looking at your screenshot I'd say the following regex would do the job but not sure :

 

(\d+)?\s?(.*)
Gamaliel
10 - Fireball

Please see attached sample data. Thanks.

Gamaliel
10 - Fireball

This one works 🙂 Thanks.

Jean-Balteryx
16 - Nebula
16 - Nebula

Here it is with my regex !

Gamaliel
10 - Fireball

Hello @jean , I am sending you another workflow. It is just that there is another problem discovered. Please see attached workflow. I added a space in the beginning of the first cell. But the correct regex does not work for it.

 

Gamaliel_0-1627525635073.png

 

Thanks.

atcodedog05
22 - Nova
22 - Nova

Hi @Gamaliel 

 

2 possible options

 

1. Use data cleanse tool before regex to remove leading and trailing spaces.

or

2. Use regex like below which handles leading spaces.

 

 

\s*(\d+)?\s?(.*)

 

 

Workflow:

atcodedog05_0-1627539415237.png

 

Hope this helps : )

Jean-Balteryx
16 - Nebula
16 - Nebula

Hi @Gamaliel ,

 

@atcodedog05 gave you the answer. I'd rather go for the cleansing solution as it makes your data cleaner and more consistent, also simplifying your regex.

Labels