Financial Consolidation and Close

Get Involved. Join the Conversation.

Topic

    Mark Drayton
    Data Management - DR CR Split on a Delimited Data File
    Topic posted September 16, 2019 by Mark DraytonRed Ribbon: 250+ Points, tagged Data Integration 
    52 Views, 6 Comments
    Title:
    Data Management - DR CR Split on a Delimited Data File
    Summary:
    Data load using a DRCR Split
    Content:

    Hi All,

    I'm attempting to import this data file using the DRCRSplit functionality within the Import Format :

    IS_7420,Opening Balance,,1000.00
    IS_7510,Fixed Asset Cost  B/F,1615414.00,
    IS_7430,Fixed Asset Cost Additions,,792114.54
    IS_7440,Fixed Asset Cost Disposals,,822299.46

    File fields: Account, Description, Debits, Credits

    In my initial attempts I have only been able to load the single DR amount using this configuration in the Import Format (image attached).

    I'm not sure whether this is possible in a delimited file or whether this is the best approach - I also want both DR / CRs to be loaded as positive values.

    Any thoughts welcome.

    Thanks,

    Mark

    Version:
    19.09.70
    Image:

    Comment

     

    • Tony Scalese

      DRCR split is only applicable to fixed width files.  You will need to pre-process the file.

    • Eldo Mills

      How about only Column=3,4 (without the DRCRSplit function)? 

    • Mark Drayton

      Interesting, this still only loads the first column.

      I've tried a number of options: different delimiters, using Column=3|4, removing the description field, checking file encoding, used "Multi Column - All Data Type" file type, additional delimiters at the end of the rows.

      Does this expression need to have a Driver and Member defined?

      Thanks,

      Mark

       

    • Eldo Mills

      Hi Mark,

       

      Use Column=3,4 and then change your  "Multi Column - All Data Type" to  "Multi Column - Numeric Data" that will add both columns.

       

      Regards,

      Eldo

      • Tony Scalese
        While this may work on a technical level, functionally, you will have inaccurate data. If you have a a credit balance in an asset account, you need that to show up as a negative number. CRDR split subtracts the credit column from the debit column. The method you outline creates 2 records which DM would naturally ADD.