0 Replies Latest reply: Jan 13, 2016 2:41 AM by Nikola Antonov RSS

    Accounting Data Transformation

    Nikola Antonov

      Hello, dear friends,

       

      We have a table extracted from the accounting database (an ordinary debit/credit transactions sorted by document and row ID).

       

      AccData:

        LOAD

        Row_ID,

        Doc_ID,

        #Debit,

        #Credit,

        Position,

        DrAccount,

        CrAccount

        Resident RowData

        Order By Doc_ID, Row_ID

      ;


      The result is looking like the following simple table (selected only one document):


       

      Doc_ID Row_ID DrAccount CrAccount Position #Debit #Credit
      126865036964424532PRODUCT10-58.03
      126865036964444532PRODUCT20-3
      12686503696441490209PRODUCT30-290.14
      12686503696443490211PRODUCT40-15
      12686503696445410366.170


      We need to transform the data in order to fill the missing fields and the result should look like this:

       

      Doc_ID Row_ID DrAccount CrAccount Position #Debit #Credit
      126865036964424104532PRODUCT10-58.03
      126865036964444104532PRODUCT20-3
      12686503696441410490209PRODUCT30-290.14
      12686503696443410490211PRODUCT40-15

       

      Could you please suggest me some scenarios how to transform the data in the script in order to get needed result?

       

      N.B. In the database it is possible to have per document only 1 debit record againts 1 or many credits OR 1 credit aganits 1 or many debits. There is impossible to have many debits againts many credits per 1 document. The Position column can be nullable.

       

      Thank you in advance!

       

      Best regards,

      Nikola