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