Accumulate in order according to the parent ID
【Problem】
I have one data set as below
final result should be like below. each deal should get the accumulated Debit and credit based on it’s parent deal id.
【Answer】
Idea: The questioner does not explain whether the original table records are inserted in chronological order or not. From the sample data, it seems that they are inserted in chronological order. Therefore, it is only necessary to traverse the sample data in order to construct a cumulative table, insert the non-parent DealID directly into the cumulative table, and insert the parent DealID after accumulating with the previous cumulative record.
A |
B |
C |
|
1 |
=file("C:/Users/debit.txt").import@t() |
||
2 |
=create(DealID,Credit,Debit) |
||
3 |
for A1 |
||
4 |
if A2.select(DealID==A3.OriginalDeal).count(DealID)==0 |
||
5 |
=A2.insert(0,A3.DealID,A3.Accounted,A3.AccountedDebit) |
||
6 |
esle |
||
7 |
=A2.select@1(DealID==A3.OriginalDeal) |
||
8 |
=A2.insert(0,A3.DealID,A3.Accounted+C7.Credit,A3.AccountedDebit+C7.Debit) |
After running:
A1:
A2:
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL