Splitting records as per length and name referring from another file

Question

I have this complex data (FileA) and I want to split its each row into separate columns referring the Reference file.

In Reference file its given- Filed name and the length

For ex- If first row is --  FS020101XWARPLCFRPT000

And Reference file's first row has- Field name= Tradetype and Length= 1 and second row-  Field name= Side and Length= 2

Then my output will have:

How can this be solved in alteryx?

FileA.xlsx

Reference.xlsx

binu_acs · Accepted Answer

@hf14 Here is the one way of doing this. I dont think the reference field length is correct but you can adjust that in the regex tool in the workflow

DataNath · Accepted Answer

Hey @hf14, here's how I'd go about this. This approach is fully dynamic if you were to add or take away from the reference file:

As mentioned above, the [SideID] column may look incorrect as the length is 1, but in the reference file you provided the length for this field was given as 1 unlike your original post where you said 2.