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|
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
If my current methodology is wrong, please help me doing in a better and efficient way. Thanks
|Loan Id||Loan Given Date||Transaction Date||Daily Deposit||Current Last Paid Dt||Expected last paid Dt|
Can you explain why the expected last paid date is 12-01-2017 for this line?
100 12-07-2017 12-07-2017 $ (100.00) 12-05-2017 12-01-2017
Because the 100$ paid on 12/05 didn't go through and got reversed on 12/07. We get a confirmation of payment only a day or two later. So on 12/07, there would be a negative entry of 100$ that day. The source tables does not go back retroactively to negate the payment on 12/05. I need to do that in ETL.
I need to check for a payment entry which is 0$ or a negative value. If it's 0$, I go back and check the day where a valid payment is done and consider that date. But when there is a negative payment, I should not consider the last valid payment. I need to consider second last valid payment, and in this case it's 12/01. It further complicates if there are two negative payments without a positive payment in between. Then I need to consider third last payment and so on. Is there a way to do that?
I know it's complicated, please let me know if you have any questions.
First of all, a great thanks to you for your quick responses to my question. I just updated the question. Please let me know if you need more information.
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
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.
This tutorial should give you enough info to extrapolate from.....
Introduction to Talend Open Studio for Data Integration.
Practical steps to developing your data integration strategy.
Create systems and workflow to manage clean data ingestion and data transformation.