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
@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 :)
@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 😅
@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
@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.