[Solved] reversal of algebraic sign in file extract / field transform

      [Solved] reversal of algebraic sign in file extract / field transform

      Hello everybody,

      I'm running into a problem which I think is quite common amongst SAP users, and I thought it would be trivial to solve.

      Situation:
      csv file is created nightly with new actuals
      ETL job should load the actuals into the cube (since extract has multiple value columns, I have a tiny SQL statement to select only one value column)

      Problem:
      since SAP shows costs positive and revenues and other incomes with a negative sign, I need to make a reversal

      I had the following ideas at which I failed at every one of them
      1. reverse the sign directly in the SQL statement mentioned above - but I did not figure out how to do it, since it didn't get the column as a number format
      2. in a Field Transform -> numberformat function and groovy function
      here I get the error "Failure in function Monatswert: Unable to execute groovy function: multiply() should be called with a number of 0 or greater not: -1

      How am I supposed to reverse the sign, if I cannot multiply with -1 ?( ?

      Does anybody have a solution or idea here?

      Thanks in advance for any help.
      To do it via SQL in Jedox was my first idea as well, and I don't even need a case statement, since all values have to be inverted.
      the problem is that I get a data conversion error if I multiply the value by -1.

      That's why I tried it afterwards with a field transform.
      If you get a conversion error you have to figure out why.
      If all values of your column are -XXXX,XX where of course X is a number, then there is no reason for conversion errors.
      Maybe you have to cast the values first and multiply after.

      I would suggest to do the conversion in SQL.
      BTW: Seaben's suggestion is also possible in SQL.

      Bjoern. wrote:

      Hi,

      no the minus is always in front

      the content of the csv-file looks like this (whereas the last value/column is the value column
      R70110;0090000000;-92628283,33


      True, you can also use a LEFT in SQL to define a CASE statement. Also, I see you have a comma as a decimal point. If this is not converted correctly, use a REPLACE statement.
      So I fiddled around a bit with it. With SQL I didn't get it going. I got to rusty in SQL to make it work. I only got syntax errors when trying to combine Case Left and Case Cast(nvarchar) left respectively.

      In the field transform the numberformat function worked but with groovy I cannot multiply with a negative number.
      I get the following error:
      "Transform T_FT_GuV wurde geprüft. Es gab Warnhinweise.
      Failure in function Monatswert: Unable to execute groovy function:
      multiply() should be called with a number of 0 or greater not: -1"


      Since I am completely new to Groovy, I made a mistake in the syntax.
      I just have now

      Source Code

      1. return _input1*(-1)
      and it works fine. There's no need for a formatnumber function either.

      Thanks for your help. It kept me going.

      Post was edited 1 time, last by “Bjoern.” ().