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

    This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

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

      The post was edited 1 time, last by Bjoern. ().