...

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


 
 •  636 View  •