Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Unable to convert negative values with minus behind value "1.24-"

  1. #1
    Join Date
    Jul 2009
    Posts
    7

    Question Unable to convert negative values with minus behind value "1.24-"

    Dear Pentaho Community,

    I'm using PDI 3.2 for a short time now and I already experienced the fantastic possibilities given by this great tool.

    However, I have a severe problem that I wasn't able to solve though I spent hours on the internet and by trial and error in Kettle itself - in the end it always ended up with an error...

    The point is that I export data from SAP to CSV files. The import and the further proceeding within PDI works well except for negative values. That is because SAP saves negatives values not like "-1.24" but like "1.24-"

    Since I was neither able to find a suitable operator / transaction in PDI nor to develop a script code that can move the minus from right to left side (recognize this input as a negative numeric / integer value) I hope that anybody could give me advise what to do, please.


    Kind regards,
    Andreas

  2. #2

    Default

    Andreas -
    Use javascript to implement the following algorithm
    If the field has '-' (indexOf), do a substr to get the numeric part for str2num(numeric_part) * -1
    If the field has no '-' then you can do str2num on the field.

  3. #3
    Join Date
    Jul 2009
    Posts
    7

    Default Solution found!

    Hi acimi thanks a million for your proposal.

    In the end I managed to use the "formula" step with the following code:

    IF
    (RIGHT([INPUT])="-";"-"&LEFT([INPUT];LEN([INPUT])-1);[INPUT])
    INPUT is the field that is being delivered by the text file input step


    However, it has to be considered that the input variable as well as the output is of type "string" in that case. Even if in the text output file step the field is set to numeric, non numeric values will be written to file.



    Cheers,
    Andreas

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.