topic Re: Make calculation based on a formula field in Talend Studio

Make calculation based on a formula field

Anonymous — Tue, 11 Sep 2018 15:48:34 GMT

Hi, I'm a newbie in Talend, I will try to explain what I want to do.

I stock calculation formula based on value getting by ID. And I want in talend to make the calculation with the formula and values and stock the result in another table.

To show my tables :

The first table with a classic field Id and Value

And the second table with formula.

My goal is to take each formula and do the math with the value of each ID in formula.
The first line of my formula's table : I want to make the calcul value of ID 001 (10) + value of ID 002 (26) = 36.
The second line : I want to make the calcul value of ID 001 (26) * value of ID 002 (52).
The last line : I want to make the calcul value of ID 001 (26) *19.5.

And I want to make the treatment for each line of formula's table and stock it in a third table.

Also the 2 tables and the formula's structure are not fixed for my work, so if you have another structure to make it possible I can take It.

Thanks if you can help me.

Cordially.

Re: Make calculation based on a formula field

fdenis — Tue, 11 Sep 2018 15:56:01 GMT

into a tMap add loockup
main is formule.
lockup is data (pivote data from column to line)
use replace function to set data into formula
Execute formula.
Done.
Good luck

Re: Make calculation based on a formula field

Jesperrekuh — Tue, 11 Sep 2018 17:07:18 GMT

Your first table with ID and VALUE, Is the ID fixed from 001 - 005 (006 not possible, or to infinity ?)
and is your Formula table also fixed or a lot of different formulas based on ID?
Why is it ID 005 isnt used in the formulas as a variable?
Is ID some sort of financial category/classification and there are only 3 types and you have some sort of transaction log?
Is it some data preparation to make reporting or pdf generation more easy?

Looks/reads like an aggregation but im missing out on the grouping... please elaborate a little more.

Re: Make calculation based on a formula field

Anonymous — Wed, 12 Sep 2018 10:17:14 GMT

@fdenisThanks to your reply.

I've try your solution, but I don't understand why I must transform my table in column to line ?

My table is an example, my definitive table will contains thousands/hundred of line (it will be complex) :

I've try this solution with replace, but I've the problem of list of values, here the result :

Maybe we can found a solution in this way.

Thanks a lot for your reply.

Re: Make calculation based on a formula field

Anonymous — Wed, 12 Sep 2018 10:30:52 GMT

@Dijke
Thanks for your answers
To precise more what I want :

-My first table have way more ID (hundred/thousands ID's). This is an example to find the best way to make possible what I want, so it's not a definitive structure or way.

-The second table with formula is not fixed, but I want to find a perfect structure to make formula's calculation possible.

- ID005 is not used because I want to show they were more ID which can be used.

- ID will be an auto-generate ID, We will have a table with Id of indicator and value of indicator (my first table), after that I will add period of indicator's value. And in our application we developp, we want to let the user create some aggregation and simple calculation with the indicators. And my idea it's to create a table that will stock the formula's (set by the user with the application) and let's Talend make the calculation after with the formula to stock the value in a third table.

So I've simplify the problem in maximum to not show some useless information.

Thanks for your reply. I hope we can find a way to make it possible.

Re: Make calculation based on a formula field

fdenis — Wed, 12 Sep 2018 11:14:53 GMT

for one formula in row1
you'll get n output one by value (row2)

sample:
for formula ID001+ID002 and ID001=1 ID002=2 ID003=3

--> 1+ID002
--> ID001+2
--> ID001+ID002

If you want to do that, it's possible but, you have to keep formula into tMap variable, replace value into this tMap variable
then you have to take the last output formula by input formula.

Re: Make calculation based on a formula field

Jesperrekuh — Wed, 12 Sep 2018 11:23:09 GMT

Solve presentation and calculation at user input/output side, I cant see the point of storing those calculated values and let Talend calculate them ...

You are trying to build a simplified calculation engine... I would avoid it at all cost.

I do understand you want to store formulas and maybe even some versioning on top of it (econometric history typed).

Take a closer look at XSLT and functions...
http://www.java2s.com/Tutorial/XML/0100__XSLT-stylesheet/Docalculationinselect432.htm

but you need to find a way to generate/construct it yourself.

However I would develop some ui component and let the end-user store the formula(s) (xslt) and store it in the backend / table or other type...

Re: Make calculation based on a formula field

Anonymous — Thu, 13 Sep 2018 14:30:48 GMT

Thank's but I don't understand, how we can take the last output and the formula to make replace in a single variable ? Or maybe create 2 variables ?

Thank's a lot for your reply.

Re: Make calculation based on a formula field

fdenis — Fri, 14 Sep 2018 07:43:44 GMT

add formula Id to the output of the tmap then add tAggregate function Last group by this id

Re: Make calculation based on a formula field

Anonymous — Fri, 14 Sep 2018 13:18:42 GMT

But I will have a line with only the last element replaced, if i take your sample :
sample:
for formula ID001+ID002 and ID001=1 ID002=2 ID003=3

--> 1+ID002
--> ID001+2
--> ID001+ID002

If I use an tAggregate on last, I will have only ID001+ID002.

Re: Make calculation based on a formula field

fdenis — Mon, 17 Sep 2018 09:15:06 GMT

sorry the last line will be 1+2

Re: Make calculation based on a formula field

Anonymous — Thu, 27 Sep 2018 09:43:54 GMT

Hi Denis,

I've try but no, the last line will be ID001+ID002 because the first line will replace ID001, the second will replace ID002 and the third line will replace Id003 but not in the formula.

--> 1+ID002
--> ID001+2
--> ID001+ID002

Now i've a job that do what I want. But I want to optimize the replacement task.

So to detail, I've change the structure of formula's table to this structure :

And I make this job :

The 1st part consist to set variable, and check the parameter we need to extract just what we need.

The 2nd part consist to replace each parameter in each formula, but I think we can make it more performant.

And the 3rd part I use the ScriptEngineManager to make the calculation and take the result.

So in the 2nd part I made this :

In the tMap, I've made a relation with filter that permit to make replacement only if we have the parameter.

So the result is like :

PARAM_1{10}+PARAM_2{ID[002]}|100
PARAM_1{ID[001]}+PARAM_2{26}|100
PARAM_1{26}*PARAM_2{ID[003]}|101
PARAM_1{ID[002]}*PARAM_2{52}|101

Then I sort by ID to treat the result in tJavaRow.

In the tJavaRow, I take each line to replace each parameter I will take in each line for the same ID :

I used global variables to stock the formula and the ID, and i make the replacement of the ID of the parameter with his value.

So I will have this kind of result :

PARAM_1{10}+PARAM_2{ID[002]}|100
PARAM_1{10}+PARAM_2{26}|100
PARAM_1{26}*PARAM_2{ID[003]}|101
PARAM_1{26}*PARAM_2{52}|101

Then I take the last row in the tAggregate, and delete with a regexp the "PARAM_*{" "}" part in the tReplace.

Then I made the calculation of each line with the result :

10+26|100 => 36|100

26*52|101 => 1352|101

Did someone have a better solution to replace each parameter in each formula ?

Thanks a lot fdenis and thanks everyone.

Re: Make calculation based on a formula field

fdenis — Thu, 27 Sep 2018 11:35:42 GMT

on your tmap, you can keep variable from one row to the others.
set formula(row5) as main link. out1 as loockup (set match model to all matches)
Add a Var named formulaId at the end of Var.
store formulaId on the Var.FormulaId.

if you do so you can set Var.Out with:
if (row5.formulaId=Var.FormulaId) then Var.Out.Replave(…..)
else row1.Formula.Out.Replave(…..)

after you just have to get the last out group by formula.

the big question is: how many formula how many params and how many time?
Talend is an ETL.
there are some others software optimized to do that (have a look to R or mapR).
you can use Talend to call R.
good luck

Re: Make calculation based on a formula field

fdenis — Mon, 01 Oct 2018 08:55:32 GMT

did you success on your task?