Sum of all historical transactions amount based on a date variable in another table

Comments

2 comments

  • Avatar
    Adrian Williams

    I'm not sure whether I have understood what you are trying to achieve, but here is a data flow that aggregates the transactions for each customer that occurred before the first Direct Debit (DD) transaction for the customer. It also identifies and aggregates transactions for customers where there were no DD transactions.

     

    First the DD transactions are sorted by Customer ID and Transaction Date. The Remove Duplicates node is then used to get the first (oldest) DD Transaction Date for each Customer ID. A Modify Fields node then adds a common prefix to all the DD transaction fields and removes the Amount field.

    The Merge node is then used to separate out any transactions where the Customer ID in the 'All Transactions' data set does not exist in the DD Transactions data set.  These transactions are summed in an Aggregate node (per Customer ID). The DD transaction fields are also excluded from the fields on the Merge node's 'matches' output.

    Where there is a match, a second Merge node is used output the 'All Transactions' records where the Transaction Date is earlier than the oldest DD transaction date for that Customer ID. This is done by modifying the ProcessRecords script for the 'matches' output pin:

    A second Modify Fields node is used to exclude the  DD transaction fields. These records are then Aggregated.

    Finally, all of the aggregated values are combined using the Cat node.

     

    The example data flow is attached below. Note, the data flow is compatible with a v.3.4.x release but is not compatible with v.3.5.0.

     

     

     

     

     

    Attached files

    Total_Non_DD_Transactions_By_Customer_ID - 27 Aug 2019.lna

     

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    And here is a version of the example data flow that is compatible with Analyze v.3.5.0

     

     

    Attached files

    Total_Non_DD_Transactions_By_Customer_ID_3.5.0 - 28 Aug 2019.lna

     

    0
    Comment actions Permalink

Please sign in to leave a comment.



Powered by Zendesk