Accumulate in order according to the parent ID

 

Problem

  https://forums.opentext.com/forums/discussion/183667/how-to-use-one-data-set-output-to-create-another-data-set-dynamically

  I have one data set as below

  undefined

  final result should be like below. each deal should get the accumulated Debit and credit based on it’s parent deal id.

  undefined

 

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:

  undefined

  A2:

  undefined