topic Re: how to make this calculation in talend tool in Talend Studio

how to make this calculation in talend tool

RAJ6 — Sat, 16 Nov 2024 09:07:00 GMT

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:

Re: how to make this calculation in talend tool

Anonymous — Wed, 01 Nov 2017 08:55:57 GMT

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/td-p/41233

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

Re: how to make this calculation in talend tool

RAJ6 — Wed, 01 Nov 2017 10:10:05 GMT

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

Re: how to make this calculation in talend tool

Anonymous — Wed, 01 Nov 2017 12:59:21 GMT

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

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

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

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

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