I am new to Talend and trying to do the following. Appreciate if someone here could guide me in the right direction.
I have a dataset like below.
Loan Id
Loan Given Date
Transaction Date
Daily Deposit
100
12/1/2017
12/1/2017
$ 100.00
100
12/1/2017
12/2/2017
$ 100.00
100
12/1/2017
12/3/2017
-
100
12/1/2017
12/4/2017
-
100
12/1/2017
12/5/2017
$ 100.00
100
12/1/2017
12/6/2017
-
100
12/1/2017
12/7/2017
$ -100.00
102
11/15/2017
11/15/2017
-
102
11/15/2017
11/16/2017
$ 75.00
102
11/15/2017
11/17/2017
-
102
11/15/2017
11/18/2017
-
Now the business logic is to find out the last paid date for each of the loans. I tried using a tmap component, it calls a java routine that has a static variable last_paid_dt which would store the transaction date when the daily deposit > 0. However, when the daily deposit is less than 0 the static var would not get changed. This works fine when the amount paid is 0.
Issue - See the red highlighted values in the table below
When the amount paid is reversed a day or after, the last paid should be from previous non-reversed positive amount. I was not able to get that done.
Also when a new loan id starts processing I need the static variable to get reset which is not currently happening.
If my current methodology is wrong, please help me doing in a better and efficient way. Thanks
Literally finished working on this issue couple weeks ago. The way we worked around this is by adding additional fields to the base transaction fact table
payment_reversed(Y/N)
Date_of_Reversal
Reversal_trans_ID
The job processes transactions as per normal. The last stage identifies reversed payments and populates the above fields. From there you can rollup or summarise the loan ID with the latest payment date easily by excluding any transactions that have a reversal flag. The base table retains the history of one or more reversed payments. The reversal ID self references back into the transaction table if full details of the reversal transaction are needed.
Thanks for the solution. Really appreciate your help. If it's not too much trouble, could you elaborate a little more on how and what components did you guys use to get this done as I am new and just learning the ropes. Thanks again.
