topic Re: Calculate a value based on previous rows values in Talend Studio

Calculate a value based on previous rows values

Anonymous — Fri, 22 Dec 2017 03:30:17 GMT

Hi,

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

Loan Id	Loan Given Date	Transaction Date	Daily Deposit	Current Last Paid Dt	Expected last paid Dt
100	12/1/2017	12/1/2017	$ 100.00	12/1/2017	12/1/2017
100	12/1/2017	12/2/2017	$ 100.00	12/2/2017	12/2/2017
100	12/1/2017	12/3/2017	-	12/2/2017	12/2/2017
100	12/1/2017	12/4/2017	-	12/2/2017	12/2/2017
100	12/1/2017	12/5/2017	$ 100.00	12/5/2017	12/5/2017
100	12/1/2017	12/6/2017	-	12/5/2017	12/5/2017
100	12/1/2017	12/7/2017	$ -100.00	12/5/2017	12/1/2017

102	11/15/2017	11/15/2017	-	12/5/2017	NULL
102	11/15/2017	11/16/2017	$ 75.00	11/16/2017	11/16/2017
102	11/15/2017	11/17/2017	-	11/16/2017	11/16/2017
102	11/15/2017	11/18/2017	-	11/16/2017	11/16/2017

Re: Calculate a value based on previous rows values

Anonymous — Mon, 25 Dec 2017 08:41:15 GMT

Hi Jaya

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

Regards

Shong

Re: Calculate a value based on previous rows values

Anonymous — Mon, 25 Dec 2017 16:10:17 GMT

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.

Re: Calculate a value based on previous rows values

Anonymous — Tue, 26 Dec 2017 08:06:49 GMT

In your source table, it is not a negative value, it is $ (100.00)?

Re: Calculate a value based on previous rows values

Anonymous — Tue, 26 Dec 2017 13:01:15 GMT

Hi Shong,

I copied the dataset from excel and so the negative value has a bracket. It is actually -100$.

Re: Calculate a value based on previous rows values

Anonymous — Tue, 26 Dec 2017 14:49:54 GMT

The data table is unreadable, please edit it or capture a screenshot.

Re: Calculate a value based on previous rows values

Anonymous — Tue, 26 Dec 2017 14:56:01 GMT

Hi Shong,

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.

Re: Calculate a value based on previous rows values

Anonymous — Wed, 27 Dec 2017 19:23:03 GMT

Hi Shong,

Any suggestions or workarounds on calculating the last paid date?

Re: Calculate a value based on previous rows values

Anonymous — Thu, 28 Dec 2017 03:31:29 GMT

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?

Regards
Shong

Re: Calculate a value based on previous rows values

Anonymous — Thu, 28 Dec 2017 03:41:22 GMT

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.

Re: Calculate a value based on previous rows values

Anonymous — Thu, 28 Dec 2017 03:52:03 GMT

Thanks for your clarification, I will make some testing and let you know the result.

Re: Calculate a value based on previous rows values

Anonymous — Wed, 03 Jan 2018 05:33:58 GMT

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.

Re: Calculate a value based on previous rows values

Anonymous — Wed, 03 Jan 2018 14:58:25 GMT

Hi TCharytanowicz,

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.

Re: Calculate a value based on previous rows values

Anonymous — Wed, 03 Jan 2018 15:34:21 GMT

This tutorial should give you enough info to extrapolate from.....

https://www.rilhia.com/quicktips/quick-tip-compare-row-value-against-value-previous-row