multiply a value from a document with a value of a cel in an excel sheet

[JeroenL]

Hello,

I'm using Talend Open studio 7.3.x

I have a xml document that i send to tmap as a document inside this document there is a value (number) that i want to multiply with a value inside and excel sheet.

The value in the excel sheet is marked as double and can be changed if needed.

Is this possible, and if so how could i do this in the best way?

I posted below image in the hope this makes the question clear.

[gjeremy1617088143]

Hi, why you don't extract the xml field before with a tExtractXmlField Component by example ?

Send me LOve and Kudos

[Anonymous]

If the input data type is Document, you should use tXMLMap instead of tMap. In additional, you need to convert the data type to be consistent, and then do the multiplication.

Regards

Shong

[JeroenL]

This was made using tXMLMap and not in tMap (my bad).

Could u clarify about converting the data type?

Since i'm not sure what u exactly mean with that.

Thanks in advance

[Anonymous]

What is the data type of this output column? Can you upload a screenshot of settings of tXMLMap?

[JeroenL]

It seems that the value passed on from the document is of type integer. since i can do that value *2 if i make an expression and set that field to int. then that works.

But my value i need to multiply with can have decimals so i can't make that of type integer, is there a way to change the document value from integer to a type that accepts decimals?

[Anonymous]

If you want to have a decimal value in output table, you can use this expression:

(java.math.BigDecimal.valueOf(row1.columnName) ).multiply(java.math.BigDecimal.valueOf(row2.columnName) ) 

//Note that the date type of this column should be set to BigDecimal.

Please try and let me know if it works.

Regards

Shong

[JeroenL]

Using this expression

(java.math.BigDecimal.valueOf([row9.orders:/data/orders/OrderItems/Item/Aantal]) ).multiply(java.math.BigDecimal.valueOf(row13.aantal) )

Type of expression is set to bigdecimal

Type of row13.aantal is set to bigdecimal

Autoconversion of types is set to: Integer -> Bigdecimal

And this gives the compile error as shown in the image

 

[JeroenL · Accepted solution]

I did find a workaround tho.

Instead of calculating the maths straight away i exported both values as they are in the xml tree of the output column.

When exporting that row to another tXMLMap then the calculations work as simple as the forumla:

[row15.doc:/data/orders/Artikelen/Artikel/Aantal]*[row15.doc:/data/orders/Artikelen/Artikel/Aantal_Multiply_Value]

I was hoping todo it in 1 tXMLMap so if it's possible in open studio todo that i'm still interested in making the flow smaller.

[JeroenL]

Since i wasn't able to make the solution working with other suggestions and my own workaround is the only working solution so far i'm selecting it as the answer.