Accounting Data Transformation

n_antonov — Tue, 12 Jan 2016 14:32:33 GMT

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
1268650	3696442		4532	PRODUCT1	0	-58.03
1268650	3696444		4532	PRODUCT2	0	-3
1268650	3696441		490209	PRODUCT3	0	-290.14
1268650	3696443		490211	PRODUCT4	0	-15
1268650	3696445	410			366.17	0

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	#Credit
1268650	3696442	410	4532	PRODUCT1	-58.03
1268650	3696444	410	4532	PRODUCT2	-3
1268650	3696441	410	490209	PRODUCT3	-290.14
1268650	3696443	410	490211	PRODUCT4	-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

topic Accounting Data Transformation in QlikView

Accounting Data Transformation