Issue with Multiple Suffixes on Joins

[BrooksGrebin]

Hello,

I am attempting to join two tables on what we'll call 'Model Number.'

My problem is that the Left table has all unique Model Numbers on their own row (which I want), while the Right table groups Model Numbers onto one row by the parent model while tacking on multiple suffixes and separating them with a "/".

So for example:

Left Table:

M25B

M25R

M25BL

Right Table:

M25B/R/BL

So a couple wrinkles in this problem is that the suffix character length isn't always the same nor is the number of suffixes per model.  What I'd like to do is make the Right Table look like the Left Table so I can join properly.  Any suggestions/help is appreciated.  Thank you!

[cjaneczko]

Do you have a table that has the base model numbers without the suffix? The issue i see is determining where the model numbers end and the suffix starts.

[OTrieger]

@BrooksGrebin 
@cjaneczko  raised a good point. When we are looking at your example it seems that what changes is the character after the last digit. If this is the logic for the whole data, then you can easily build something based on that logic.  

[cjaneczko]

Yeah, if all of the model numbers end in Digits this should be pretty straight forward. But if some of them end in a letter then its followed by a suffix its going to get much trickier. 

[acotta17]

Hi @BrooksGrebin,

This should help you get the output you desire: 

[Screenshot and workflow attached below]

1. Use the Formula Tool with the REGEX_Replace function -
Formula1: REGEX_Replace([Field1], "([A-Z]+[0-9]+)[A-Z].*", "$1")
Formula2: REGEX_Replace([Field1], "^[A-Z0-9]+([A-Z])/([A-Z].*)", "$1/$2")

2. Use the Text To Columns Tool and select the split to rows with "/" as the delimiter

3. Then use the Formula Tool to concatenate the output to get your result

You can then use the join/find and replace tool to get your final output.

Hope this helps!

[BrooksGrebin]

Unfortunately, no.  MY two options are what I've laid out already.  Is there a match I can do on the first 'X' characters?  Another unfortunate aspect to this is not all model numbers are the same length.

[OTrieger]

@BrooksGrebin 
The length is not as important as how to define the parent model. Does all parent model will end with a digit and the character after the digit will indicate the child?

[BrooksGrebin]

@OTrieger

Correction I need to clarify: The Parent Model Number is the first 4 digits.  I imagine this makes this problem a lot easier.  So what I'm looking for then is to pair the first 4 digits to the rest of the model number for each iteration.

For example: Parent = Z25S

Child iterations:

Z25SARBL/BU/R

Z25SABBL/BU/Y

This presents a slightly different challenge in addition to the suffixes as the portion before the suffix and after the Parent Model Number can vary as well.  I hope this didn't make it more complicated.

[apathetichell · Accepted solution]

two easy options:

1) create a specific match field in your datasource with parts (ie left([field4]). use join.

2) skip join - > use the find/replace tool. find/replace will support an "in this field" match - vs an exact match..