Data360 Analyze

Hi,

What I am having at the moment is:

1. A table with Direct Debit transactions, with amounts and date of the transaction and the customer id.

2. A table with a complete history of all transactions of all customers

What I now need to add to the first table is the total EURO_AMOUNT of the customer taken into consideration of all transactions that took place before the direct debit (so based on the date insert of the direct debit). So, I need some sort of sumif(historical transaction has same customer id and date insert id before the date insert of the direct debit).

I am pretty sure that I need the transform node to do this, but don't know how. Could you help me out please?

Best regards,

Erik Bruin

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

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