Make calculation based on a formula field

[Anonymous]

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.

[fdenis]

sorry the last line will be 1+2

[Anonymous]

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.

[fdenis]

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

[fdenis]

did you success on your task?