Input text file - merge header data with detail records

[oracleoftemple]

I have a series of records that look like this:

I need the header (first) row's data (really just the 10 digit number at the start) to be repeated for each detail record.  So the result should look like this:

Can anyone help me with this?

[danrh · Accepted solution]

I'd suggest some RegEx in a Multi-Row Formula tool:

IF RegEx_Match([Row-1:Field1],'(\d{10}).*') THEN
RegEx_Replace([Row-1:Field1],'(\d{10}).*','$1')
ELSE [Row-1:New Field] ENDIF

The "IF" portion of this checks if the previous row starts with 10 digits. If it does, it grabs those 10 digits for the new field. If not, it just copies down what the previous row's result was. Hope it helps!

[oracleoftemple]

I'm getting an error that says "Unknown variable "Row-1:Field1".  Can you tell me what I'm doing wrong?

[oracleoftemple]

Never mind.  I copied your text, but your field was named "Field1" and mine was named "Field_1".  I fixed it.

[mdorio]

Hi @danrh ,

I have a slightly different problem I was hoping you could help with. 

• I have header and detail records.
• My header record has the transaction date at position 10.
• The detail record has the transaction date at position 202.
• Sometimes the date is missing from the detail record.  In those cases, we would like to use the transaction date in the header record.

Do you have any thoughts on how to do this?

Thank you in advance for your help!

Mike