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]

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

[Jesperrekuh]

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.

[Anonymous]

@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.

[Anonymous]

@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.

[fdenis]

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.

[Jesperrekuh]

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

[Anonymous]

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.

[fdenis]

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

[Anonymous]

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.