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 | #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.
Thank you in advance!
Best regards,
Nikola
