Batch Macro to Parse Fields

Question

Hello Brilliant People,

I am having an issue with my batch macro. In my workflow I had to parse to rows first to generate the number of possible code combinations and then parse to columns for some downstream checks. I need the batch macro because my sequence numbers will be variable. My macro output is processing each sequence separately and not stay on the same row, which is what I need for compliance checks later.

Macro Set Up

What I'm trying to accomplish:

What the Data needs to look like

What is coming out of my macro:

Macro Output

I have been messing with this for too long at this point, but my hyper fixation is getting the best of me.

Testing Workflow Package.yxmd

KGT · Answer

I think the part that is missing is what the final output looks like. So, in the workflow I shared, I put all the sequences in one column so that the parse could be done once. and then cross-tab that back. I don't know whether the first element of Sequence1 should be listed in the same row as the first element of sequence2 etc. I constructed the table with all the first elements of the sequence on one line and the second elements on the next line etc.

RCurry1230 · Answer

@KGT I sincerely appreciate your time on this. My process is far from perfect, so I'll take Trx103 as an example.

The columns 1,2,3 are the sequence numbers in the transaction. All 3 must be done.

In my multi tool process, I parsed to rows on the "|" delimiter for each column.

Then, I parse to columns on any "*" delimiter.

Not a terrible solution for 3 columns, but my dataset has an unknown number of sequences for each transaction.

After the parsing party, I run two filters to check conditions on the parsed fields to find which combination of codes is actually compliant.

My macro is doing each step individually, so by the end of it, I would have a column 1 will everything parsed to rows and also column 1 is not parsed but column 2 is...

being a novice macroista, my brain just cannot comprehend where I'm going wrong.

KGT · Answer

OK, I've looked at this for too long and not entirely sure how the output differs from the following. But you've stated that you don't want that (Even though that looks exactly like your "What the data needs to look like), or is it just that it couldn't be figured out that way.

Data: X*Y|Z|A|B|C

Split Data to rows on |

Split data to columns on *

Create the header

Cross-tab

The other questions I have are:

* Do you want the data so that there is every combination of sequences in separate rows (Ex. TXN Type 100 in your screenshot, Parse2_1 is the same value repeated for each line), or do you just want Sequence 1/2/3 on the same row

* Can there be up to 7 sequences? Is that why there are columns 1-7?

OK, as for the macro:

* Turn field mapping off on your Macro Input as it creates confusion and is not needed here.
* If you were to do it this way, your batches would need to be unique combinations of TXN Type and Sequence.
* The Control Parameter is updating 3 tools, and some of them won't work properly. You are feeding the Control Parameter (TXN Type) in and then splitting that field each time, when you actually want to split your crosstabbed fields.
* Do the split to rows before crosstabbing the sequence columns.
* Remove the Auto-field. This is great to figure out what's happening, but you know the fields your expecting and so replace it with a select.

My advice:

Get the process right for one instance of TXN_Type, and then you can just batch on that. Otherwise get it right for one instance of TXN_Type/Sequence and then batch on a field that represents them (This will require putting the data back together)

I've attached your package (without your macro as I would have had to resave it in an older version for my version to package, so I just excluded it.

In that I've split [TXN Type]=103 in a way that I understand it should be done (but doesn't align with your screenshot), and there is a macro example that is not finished. I had a crack, but wasn't sure on the output so abandoned it. Take a look and you may be able to steal some of the methodology. Transpose will be your friend here.

Testing Workflow Package - KGT edit.yxzp