Need help with RegEx Field Parsing

Regex

Accepted answers

OK! I ditched the idea of a macro. All you really need is a concatenation of the following records in certain cases. So I used the Multi-Row tool to build an identifier of the groups of records then the Summarize tool to concatenate the Unit #s. I wasn't sure if you would need the original Record field, so I used a separate stream to bring them back into the data.

Bren312 part 2.png

Happy hunting and good luck!

Best,

David

(edit: added attachment)

All comments

vizAlter

Hi @Bren312 — It's good to attach sample/dummy data to know more about your Input file and desired output file, both.

Your attached file does not have data:

Crud; sorry about that! I just reposted the workflow with dummy info in a Text input so hopefully that fixes it.

@Bren312 !
It took some cleansing to get it to a point where the RegEx wouldn't be stupidly complex.

If you have a need for more than two extra rows of unit numbers then I would wrap the Multi-Row Formula tool into an Iterative macro. If you need, I can help with that, should the need arise.

That was an interesting challenge.

Good luck!
David

@David-Carnes

Hi David; first off you’re a freaking pro and I really appreciate you looking at this (and especially the container name you used).

This is super close to what I’m trying to achieve but there’s a few things that need to be tweaked if you’re up for some additional headaches.

First, as you said there will be cases where I’ll need more than two rows of unit numbers...an example is rows 17 - 33 from the Input. The Buying # is the same for these records (“2865-06”) and ideally all of the units (from “01” in Row 17 to “9006” in Row 33) would be represented in the final output.

Also (and this one is weirder) I noticed some odd stuff in the results for Input Row 898 (Record 209 in the final output) for 83784-00 RIGGIO DISTRIBUTION CO such as

The Store displayed is 29, which isn’t in this record (the Store should be 65)
The Client; Status; and Unit # Columns are blank
Strangest of all, when I filter at the start of the workflow for “Records <900” the Client; Status; and Unit # Columns are populated but when I filter for “Records <901” these Columns are blank...doesn’t make a lick of sense to me.

Like I said, if you feel like letting me steal some more of your time I’d really appreciate it, and if not this will give me a great base. Thanks again David!

Bren!
I can see one mistake I made; I assumed the Buyer # would be 3 or 4 digits before the dash but "83784-00 RIGGIO DISTRIBUTION CO" proved me wrong

In the first RegEx tool, the expression I used is (\d{3,4}-\d{2})\s+([\w\s]*)\s+(\d{2})\s+([\d-]*)(.*$)

It really should start out with (\d{3,5}-\d{2}) or better yet (\d+-\d{2}) if the first set of digits might exceed 5.

Also, I did assume the second set after the dash would always have two digits. If this is an incorrect assumption then change it accordingly.

Make the change I mentioned in the second paragraph and see if that fixes the errors for 83784-00.

I can address the iterative macro later tonight.

Ciao,

David

Aces; thanks David! I was just barely savvy enough in RegEx to change the Buyer # before you mentioned it, but I appreciate you letting me know. You are correct about the digits after the dash always being two digits. Thanks again!

Bren

Bren312 part 2.png

Happy hunting and good luck!

Best,

David

(edit: added attachment)