Trying to create a Dynamic RegEx Macro

[EJ5916]

I have a Fixed Width text file that was downloaded from our mainframe.  

There could be up to 5 different "Record Types" in this one text file, they are defined by the first field [Record Type].  

Record Type Field_2    *   

PA22109                     0024057202402140346AN9A                    2024021320240213PO

DE0E9D                    3XS000001663250                          01                  0877                                                                                                                                                                   

Each record type has a different set of columns.  My goal is the get this flat text file into a readable Excel file so our staff can answer questions the client has regarding the data. 

I THINK I want to use the RegEx Tool to help parse out the data in [Field_2] for each Record Type.  I have no experience with RegEx so I've been scouring google looking for help to understand what I'm doing. 

I got my PA line to parse beautifully when I manually enter the Format to Convert Regular Expression text box to:  (.{2})(.{24})(.{7})(.{8})(.{4})(.{24})(.{8})(.{8})(.{1})(.{1})

My question is:  Can I use a dynamic formula created field to enter this expression?  

I've attempted to create a macro that has two input nodes, one for [Field_2] and the other for the formula field.  I have one output node to display the parsed fields.

I'm getting two errors on the macro when I run the workflow:

1. UpdateRegEx (15) Record #1: Tool #2: 11 Marked Groups were found, but 1 fields were defined.

2. UpdateRegEx (15) The output connection "Output18" was not valid

I'm open to ideas to make this work. I can manually enter the string but I was hoping to make this more dynamic so the end user could define any file structure by supplying a separate schema document.

Thanks for your ideas!

[apathetichell]

you have one output field in your regex tool. you have 11 marked groups. Alteryx does not know what to name your 11 marked fields.

[apathetichell]

This gets you a little closer - but you should look at a nested batch macro and a logic so you know which regex to apply to which recordid.

[EJ5916]

I don't understand what you did here or how this flow is better/different than mine.  Can you explain? 

However, your comment about it not knowing the names was very interesting.  My layout document has the field names included, perhaps I need to pass that list to the macro as well to apply to the parsed out fields?  But I don't think the parsing is working based on my passed in variable so I might not get too far with it. 

[apathetichell]

Sure. Mine works because:

1) I do not crosstab/summarize join my matched group. I feed 1 matched group at a time into the macro - this is because your regex tool is set up to match 1 group. It will not match multiple groups - it will match 1 group. Your regex tool can be set up to match any number of groups - but this is coded at the time you set up your regex tool. When you use your action tool to change your match logic - the tool does not also change the number of output fields. That would have to be changed via editing the raw xml - and using some kind of generate rows, and creating a multi-layered matched batch macro. and it would be a huge pain.

2) To get around this - I have each row as a specific matched group. One matched group. One regex output (!!)

3) To get around the issue of how we know which group we are matching for. I use a running total and then a multi-row formula tool. This sets the specific matched group as a successor to the prior matched group (and ignores what came before).

To get this to match multiple fixed width rows - with differing schemas - you would need to assume they all have the same number of final columns - or you are o.k.with nulls in some of the columns. You would put this outer macro into ANOTHER macro.

You would create a map between regex matched groups (that feed into your batch group) in your outer workflow.

You would add a filter to both inputs to the batch macro in this workflow.

The filter would determine which rows/matched fields were being matched.

[apathetichell]

Are we solid here - can you mark my solutoin as correct because:

1) It works.

2) I explained what you would need to do to scale this for other solutions.

3) I explained why a multi-marked group regex cannot replace a single marked group regex (and vice versa)

[EJ5916 · Accepted solution]

I wasn't able to get your macro to work so I went a different route and I got that route to work for now. It's not as dynamic as I would like but the output is good. My next task is to try to automation it using an app.  Wish me luck!  :)