Forum Discussion

JohnHenry's avatar
JohnHenry
New Contributor II
2 years ago

How to load field from a CSV Header record to all imported Detail records in the file

I'm loading a Fixed Width data file to Stage for Transaction matching. 

This file has:

  1. HEADER record that has the transaction_date in it.
  2. multiple DETAIL records with transaction type and amounts (but no dates).
  3. TRAILER record (Ignore for this sample)

Goal - Load the DATE from header record on every detail record.

Sample File

HDR01122023
DTL0088351000000572975A000000018

DTL0088351000000123456A000000018
TRL000000

What I want to load into OneStream

Record_1:

Date: 01122023

Tran Type:  100

Amount: 572975

Record_2:

Date: 01122023

Tran Type:  100

Amount:  123456

 

Data Sources
  • JackLacava's avatar
    JackLacava
    2 years ago

    One technique for this sort of thing. is to set a Business Rule on the Amount dimension, so it runs on every line. When it finds the "date" line, it stores the value in the Globals object. Then, another rule assigned to Time simply retrieves and returns that value.

  • JackLacava's avatar
    JackLacava
    Honored Contributor
    2 years ago

    One technique for this sort of thing. is to set a Business Rule on the Amount dimension, so it runs on every line. When it finds the "date" line, it stores the value in the Globals object. Then, another rule assigned to Time simply retrieves and returns that value.