Inconsistent data format causing Text to Column anomalies

Hi Team,

Need your help

I have data coming in via different externals system and majority (90%) the format of the "External system ID" is in the following format "ABC_123456_Mason"

Consequently i have used the Text to Column functionality to split the data into 3 columns anytime it find an "_" (See below SC)

External System	External ID	Column A	Column B	Column C
Mason1	ABC_123456_Morgan	ABC	123456	Mason
Mason2	AVI_600291_CV15NR	AVI	600291	CV15NR

However there is one external system "Mason3" which does it a bit differently and provides data into the following format "AAA_123456Morgan". Which means anything after the first "_" is bundled into column B

External System	External ID	Column A	Column B	Column C
Mason1	ABC_123456_Morgan	ABC	123456	Mason
Mason2	AVI_600291_CV15NR	AVI	600291	CV15NR
Mason3	MAS_461598TA44NG	SKA	461598TA44NG

Just for an external system "Mason3" is there a way to extract the first 6chars after the "_" and put into column b, any other digits after the 6 digits go into column c ?

Expected Outcome

Primary System	External ID	Column A	Column B	Column C
Mason1	ABC_123456_Morgan	ABC	123456	Mason
Mason2	AVI_600291_CV15NR	AVI	600291	CV15NR
Mason3	MAS_461598TA44NG	SKA	461598	TA44NG

Looking forward to your help

Many thanks

Common Use Cases

Accepted answers

All comments

ShankerV

Hi @Masond3

From this output extracted, I would recommend you to do the formula tool.

Save the output in Column B

If length is >6 for Column B

then Left(column b,6)

else column B

Save the output in Column C

If length is >6 for Column B

then right(column b,6)

else column C

External System	External ID	Column A	Column B	Column C
Mason1	ABC_123456_Morgan	ABC	123456	Mason
Mason2	AVI_600291_CV15NR	AVI	600291	CV15NR
Mason3	MAS_461598TA44NG	SKA	461598TA44NG

Many thanks

Shanker V

Felipe_Ribeir0

Hi @Masond3

One way of doing this

Inconsistent data format causing Text to Column anomalies.yxmd

Masond3

@Felipe_Ribeir0 So do you think its best to do the text to columns as first step and then apply the formula solution afterwards ?

ShankerV

@Masond3

I suggest to Split in to columns and then apply the formula.

As for huge dataset, it might be Mason#, mason4 ...5 etc.

Many thanks

Shanker V

ShankerV

Hi @Masond3

Please find the use case below.

Many thanks

Shanker V

Felipe_Ribeir0

Hi @Masond3

Fair question!

Thinking better, it seems like a good idea to add the necessary _ to the column and use the text to columns after it. I personally would go with this idea:

If you need to keep the External ID as it is, you can use the formula to create a new External ID with the _ just to parse it correctly.

Inconsistent data format causing Text to Column anomalies.yxmd

OllieClarke

Hi @Masond3

You can use the RegEx tool here in parse mode:

The following RegEx will parse 3 columns, A with everything before the first underscore, B with all the digits after that, but before the next underscore (if there), and C with everything after the final underscore (if there) or first non-digit to the end if no underscore

(.+?)_(\d+)_?(.+)

Hope that helps,

Ollie

JoshuaB

Me personally, I would use @OllieClarke design pattern as it 1 requires less work to create but it's also more robust.

Felipe_Ribeir0

Generally the regex path leads to a higher computational cost than solutions using simple components, when the second case is possible. With the Enable Performance Profiling it is possible to see that. Ofc this will not be a problem considering small datasets, but i think that this is something to keep in mind.

Inconsistent data format causing Text to Column anomalies.yxmd

OllieClarke

@Felipe_Ribeir0 it's a very good point. I do tend to overuse RegEx. I guess it depends on the patterns of the data, looking for digits rather than exactly 6 characters could be beneficial, but it could also be incorrect. As with most things in Alteryx, it's good to review multiple approaches and pick the one which suits your use case

Felipe_Ribeir0

@OllieClarkei agree with you, it is great to see multiple approachs to solve the same problem! And i also agree that your solution does not rely on the character quantities, so it is much more flexible if there is no certainty about this fixed amount of characters between the _.

Just to share something cool that motivated me to compare the regex performance with other methods, i recently discovered that cloudfare went out for 27 minutes in 2019 because of a regex expression (poorly written, but still a regex expression).

https://blog.cloudflare.com/details-of-the-cloudflare-outage-on-july-2-2019/

Of course most of us are not dealing with a volume of data that could cause something like this, but even considering that, i found this mindblowing 😅

OllieClarke

@Felipe_Ribeir0 I've had RegEx tools error before due to excessive backtracking. First time it happened I had no idea what was going on lol

Masond3

@Felipe_Ribeir0 @OllieClarke These are all both valid scenarios.

Throughout the week i been testing to determine whats the best option. By applying a formula it enbabled me to more control based on a given external id. However @OL Your solution it also very valid.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703