LAE

 View Only
  • 1.  Use Agg Ex inside Do-while loop

    Posted 09-09-2019 09:58

    Hi all,

    I am new to the forum, so if this post is not supposed to be here, please redirect.

    I have a list of records (as shown in the attached file). I am trying to determine the Duration of the account based on the start/end date of the contracts under the accounts.

    The logic that I use is: the start date of the first contract is also the start date of the account. if start/end date of the 2nd contract is within the start/end date of the 1st contract, then do nothing. However, if end date of the 2nd contract is greater than that of the first contract, use the end date of the 2nd contract as the end date of the account. Continue to use this logic until all contracts are checked.

    The final result of this will be:

    For account A1, start date is: 1/1/2015, and end date is 12/31/2016

    For account A2, start date is: 1/1/2015, and end date is 6/30/2016

    I have tried using a Do-while loop within a Do-while loop, but unable to get the logic to work right. Please helps.

    Much appreciated.

     

    Attached files

    Contract sample 2.xlsx

     



  • 2.  RE: Use Agg Ex inside Do-while loop

    Employee
    Posted 09-12-2019 03:23

    Hi Toan,

    you don't need to use a looping construct to determine the account start and end dates.

    In the example attached after importing the data from the Excel file the START_DATE and END_DATE fields are converted from Unicode type to Date type in a Filter node. The data is then sorted by Account ID, Contract ID and START_DATE.

    Within the Agg Ex node, the START_DATE and END_DATE are stored for the first record in each group (where the group is denoted by the Account ID). The logic then compares the END_DATE for each record in the group with the stored end date and, if the current END_DATE is after the stored end date, the stored end date is updated. The account date information is output when all records in the group have been processed.

     

     

    Attached files

    Calculate_Account_Duration--share.brg

     



  • 3.  RE: Use Agg Ex inside Do-while loop

    Posted 09-12-2019 08:39

    Thank you so much Adrian!

     

    Toan