topic Re: [resolved] Read expression/formula from excel file in Talend Studio

[resolved] Read expression/formula from excel file

Anonymous — Tue, 26 Apr 2016 08:28:18 GMT

Hi folks,
I'm quite new to Talend and would like to read an expression from a given file and then execute it in tMap. The expressions are stored in a separate excel column like this:

row7.value1 + row7.value2 == 0 ? 0 : row7.value1/(row7.value1 + row7.value2)

I accomplished to read it as a string by global map: ((String) globalMap.get("row6.column_that_contains_expr")). But I'm not able to execute the expression in order to get the resulting value.
My job looks like this:

tFileInputExcel =row6=> tFlowToIerate => tPostgresqlInput =row7=> tMap => tFileOutpuDelimited

Could you please tell me how to execute the expression from file and obtain the resulting double?
Greetings
Simon

Re: [resolved] Read expression/formula from excel file

Anonymous — Wed, 27 Apr 2016 08:26:05 GMT

Hi,

row7.value1 + row7.value2 == 0 ? 0 : row7.value1/(row7.value1 + row7.value2)

Could you please show us some screenshots of your excel file with expression/ formula cell? Can you read your excel file by a simple work flow: tfileinputexcel-->tLogrow?
Best regards
Sabrina

Re: [resolved] Read expression/formula from excel file

Anonymous — Wed, 27 Apr 2016 09:20:33 GMT

Hi Sabrina,
thanks for your reply. This is how the excel file looks like:

The column "ETL_EXPRESSION" contains the expression I want to execute in tMap function. When I use ((String) globalMap.get("row6.ETL_EXPRESSION")) I only get the expression itself but not the calculated (double) value.

Starte Job talend_test am 10:16 27/04/2016.
 connecting to socket on port 3398
 connected
KPI_ID: 100020|KPI_NAME: example_kpi|ETL_EXPRESSION: row7.value1 + row7.value2 == 0 ? 0 : row7.value1/(row7.value1 + row7.value2)
 disconnected
Job talend_test endet am 10:16 27/04/2016.

Greetings
Simon

Re: [resolved] Read expression/formula from excel file

Anonymous — Wed, 27 Apr 2016 10:43:15 GMT

You can't do this.
row7.value1 + row7.value2 == 0 ? 0 : row7.value1/(row7.value1 + row7.value2) is simply a literal String.
You want it to be compiled Java code but it can't be.

Re: [resolved] Read expression/formula from excel file

Anonymous — Wed, 27 Apr 2016 10:54:05 GMT

Hm, I can't store the expression from excel in a variable and then execute it? I used that feature in other BI-Tools very often (QlikView for example).

Re: [resolved] Read expression/formula from excel file

Anonymous — Wed, 27 Apr 2016 11:33:10 GMT

How does the tool know that the variable is holding an expression and not a String? I suspect that this is a niche piece of functionality used by QlikView. A bit of Googling also shows that the Excel is also used as part of the framework and not a consumable, which it is in Talend. If you want to store equations/functions/methods somewhere in Talend, you can use the Java routines (basically you can write them into Java classes). In fact, you can use ANY Java API you want with Talend.

Re: [resolved] Read expression/formula from excel file

Anonymous — Fri, 29 Apr 2016 11:21:26 GMT

Hi,
I tried the following in tJavaRow:

ScriptEngineManager mgr = new ScriptEngineManager(); 
ScriptEngine engine = mgr.getEngineByName("JavaScript"); 
String infix = "row7.value1 + row7.value2 == 0 ? 0 : row7.value1/(row7.value1 + row7.value2)"; 
context.test = engine.eval(infix);

Which leads to an error:

 javax.script.ScriptException: ReferenceError: "row7" is not defined in <eval>

Any Ideas?

Re: [resolved] Read expression/formula from excel file

Anonymous — Fri, 29 Apr 2016 13:10:37 GMT

I don't understand what you are trying do here. All you need to do to assign the value to the context variable is....

context.test = (row7.value1 + row7.value2 == 0 ? 0 : row7.value1/(row7.value1 + row7.value2));

By wrapping " row7.value1 + row7.value2 == 0 ? 0 : row7.value1/(row7.value1 + row7.value2) " in quotes, you are essentially just creating a String.
The ScriptEngine object you are using is trying to run JavaScript. This is a scripting language which has no relationship to objects created by Talend (ie row7.value1, etc). These are Java objects.
If you are trying to use data driven dynamic transformations, you can do that, but you will need to either build them using tMap (or similar Talend components) or write Java classes (routines in Talend) to achieve this.

Re: [resolved] Read expression/formula from excel file

Anonymous — Fri, 29 Apr 2016 13:36:08 GMT

All I'm trying to do is to read an (Talend) expression from a flat-file and then execute it in tMap. But like you said it's interpreted as a String... So I guess I've to write my own classes and methods in Talend that would be able to interpret the formula I get from the file - like you suggested. Thanks!