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
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.
Hi
Can i understand like this? When it is a negative value, the expected last pay dt will be the first valid payment date for each loan id? I see 12/01 is the last third valid payment date in your case?
Sorry, my mistake. It should be 12/02 not 12/01, as the payment on 12/05 was not successful and the valid payment before that was on 12/02. Also when a new loan id is started, my date has to get reset which is not happening right now.
