Calculate a value based on previous rows values

Five Stars

Calculate a value based on previous rows values

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
Community Manager

Re: Calculate a value based on previous rows values

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

----------------------------------------------------------
Talend | Data Agility for Modern Business
Five Stars

Re: Calculate a value based on previous rows values

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.

 

 

Community Manager

Re: Calculate a value based on previous rows values

In your source table, it is not a negative value, it is $ (100.00)?
----------------------------------------------------------
Talend | Data Agility for Modern Business
Five Stars

Re: Calculate a value based on previous rows values

Hi Shong,

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

Community Manager

Re: Calculate a value based on previous rows values

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

----------------------------------------------------------
Talend | Data Agility for Modern Business
Five Stars

Re: Calculate a value based on previous rows values

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. 

Five Stars

Re: Calculate a value based on previous rows values

Hi Shong,

 

Any suggestions or workarounds on calculating the last paid date? 

Community Manager

Re: Calculate a value based on previous rows values

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


----------------------------------------------------------
Talend | Data Agility for Modern Business
Five Stars

Re: Calculate a value based on previous rows values

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.
Community Manager

Re: Calculate a value based on previous rows values

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

----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars

Re: Calculate a value based on previous rows values

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. 

 

Five Stars

Re: Calculate a value based on previous rows values

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. 

Sixteen Stars

Re: Calculate a value based on previous rows values

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

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