Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
n_antonov
New Contributor

Accounting Data Transformation

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

Tags (3)
Community Browser