Six Stars

how to make this calculation in talend tool

Hi Folks,

 

In my excel have date,type,weight and point column. I need to create previous weight column

How to make below calculation in talend tool

Step 1:date(01-01-2016)
based on first date only weight as previous weight(new column)

First day i don't need any calculation

 

Step 2:date(02-01-2016)
consider first date previous weight for second Date(02-01-2016)

Then i need to do below calculation

value=(previous weight * point)+previous weight

 

Step 3:date(03-01-2016)
consider second date value for Third Date(03-01-2016) previous weight

 

Step 4:date(04-01-2016)
consider third date value for fourth Date(04-01-2016) previous weight

 

Step 5: Again step repeated upto the date

It's like SQL Lag function.

i need to create below like screenshot:

Screenshot (129).png

3 REPLIES
Six Stars

Re: how to make this calculation in talend tool

Hi there,

 

Talend really isn't suited to working with data in spreadsheets like this, but in terms of your calculations, you would be able to expand on the solution I've provided to your LAG function question, here:

 

https://community.talend.com/t5/Design-and-Development/How-to-use-SQL-Lag-Function-concept-in-talend...

 

You will need to know a bit of Java (which is important for everyone using Talend) but it's mostly just simple expressions for these calculations, and all the basic concepts you require can be found in this simple solution.

 

 

Regards,

 

 

Chris

Six Stars

Re: how to make this calculation in talend tool

Dear  ciw1973,

  • Thank you I really appreciate your help
  • whether it is possible to shift previous value based on date 
  • we don't change date 01-01-2016 previous weight. 
  • weight = previous weight(01-01-2016)
  • 02-01-2016 date itself we need to set previous weight
  • i need below like screenshot

Screenshot (135) - Copy.png

Six Stars

Re: how to make this calculation in talend tool

You can do this by using a Dictionary to store multiple previous values instead of just a single Integer variable.

 

LagFunctionJob2.png

 

The tFixedFlowInput now has two columns, one for the type and one for the weight:

 

tFixedFlowInput4.png

 

As before, we introduce a column for the previous weight in the output schema of the tJavaFlex:

 

tJavaFlexSchema4.png

 

And the code now stores the previous weight for each type in a Dictionary:

 

tJavaFlexCode2.png

 

 

java.util.Dictionary<String, Integer> previousWeights = new java.util.Hashtable<String, Integer>();
row2.previousWeight = previousWeights.get(row1.type);
previousWeights.put(row1.type, row1.weight);

 

I'm deliverately ignoring the date side of things here, as I'm assuming they may not be consecutive, and you simply want the previous weight for each type.

 

Regards,

 

 

Chris