Filtering a column by number of characters or numeric

[mattcoleman]

I have a column in my dataset with manufacturing lot numbers. I need to decode the lot numbers into things like shift, line, month etc. We changed the way we generate lot numbers a few years back, and the decoding is different, so I have to separate them out. I was thinking of filtering by number of characters, but I may be able to use the numeric portion to filter as well. One lot number is only numeric, whereas the other is alphanumeric. All ideas welcome. Thanks!

Old Lot Number (7 chars, alphanumeric): 11Z4216

New Lot Number (11 chars, numeric): 1612234131

[Thableaus]

Hi @mattcoleman 

It'd be better if you specified what your desired output is.

It seems to me you want to parse this Lot Number to a lot of columns (shift, line, month).

What are the rules? Could you share us an example?

Cheers,

[mattcoleman]

Yes, @Thableaus that's exactly what I'm trying to do. Maybe separating them out isn't necessary, but they are mixed together in a column called "Lot Number".

EXAMPLE

Old Lot Number (7 chars, alphanumeric): 11Z4216

11_Z_4_2_16

Month: 11, Novermber

Year: Z, 2018

Mfg Line: 4

Shift: 2

Date: 16th

New Lot Number (11 chars, numeric): 1612234131

16_12_23_4_1_3_1

Year: 2016

Month: 12, December

Date: 23rd

Mfg Line: 4

Batch: 1

Location: 3, Japan

Config: 1

[Thableaus · Accepted solution]

@mattcoleman 

Fortunately, there's a very useful tool called RegEX that can ease your job.

Once you have the pattern, you can tell the tool what your parsing output should be.

I did it for both (Old and New) Lot Numbers. See solution:

Old Lot Number RegEX (Regular Expression):

(\d{2})([A-Z])(\d)(\d)(\d{2}) 

2 numbers, a letter, one number, one number, 2 numbers. That's what you're telling the tool to parse.

Then you define the column names in the configuration window, and it's done.

New Lot Number RegEX:

(\d{2})(\d{2})(\d{2})(\d)(\d)(\d)(\d)

2 numbers, 2 numbers, 2 numbers, one number, one number, one number, one number

Same thing as Old Lot Number.

Workflow appended.

Does this work well for you?

Cheers,

[mattcoleman]

@Thableaus this is great, thanks! I knew I'd have to learn RegEX at some point.

My only additional question would be....how do I tell RegEX that it's an old vs. new lot number? They are in the same column dispersed.

[Thableaus]

@mattcoleman 

Ok, so the best thing is to separate them into columns.

For that, you can use Regex_Match.

Check this workflow appended and see if this would fit.

Cheers,