Skip to Content

Turn text to number during OData data ingestion

I have an OData data source. It has an attribute which is formatted as text (and for some reason that cannot be changed), and it contains numbers (formatted as text). I know that there is a ToNumber function in SAC (https://help.sap.com/viewer/00f68c2e08b941f081002fd3691d86a7/release/en-US/eac3111c11174776856d9c1fb56f7679.html), but as I (novice SAC user) understand this can only be used in a story. I want to ideally perform this during the OData data ingestion, as the data is refreshed, copying the text data into a new SAC attribute which is formatted as a measure, being filled with numbers converted from the text data.

Any idea?

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Posted on Oct 23, 2019 at 07:55 PM

    Hi Oliver,

    Data transformations can be performed when creating the model. When new data is loaded into the model these transformations will be applied. The transformation you describe can be split into multiple steps. Not sure if all of them can be fulfilled but you should give it a try.

    Kind regards,

    Martijn van Foeken | Interdobs

    Add a comment
    10|10000 characters needed characters exceeded

    • Thanks Martijn!

      Do you have any more detail on this. Such functions doe not seem to be available among the "Transformation" rules (where you can e.g. trim an split a column) ... so where would I do this task of the text > number conversion, and to make that column a measure?

      Best, Oliver

  • Posted on Oct 24, 2019 at 08:33 AM

    Hi Oliver,

    When you create the model you can simply define whether a column containing data should be created as a measure or dimension. So with a single click, the text attribute containing numbers can be defined as a measure.

    Kind regards,

    Martijn van Foeken | Interdobs


    measure.png (355.4 kB)
    Add a comment
    10|10000 characters needed characters exceeded

    • I realize I did not paint the whole picture. That attribute also has text data in it. SAC does not allow to turn such a column into a measure. But yes, later in a story I can use the functions for ToNumeric (or whatever it is called again) and turning a dimension into a measure. I just want to do it in the beginning somehow, as I build the model.

  • Posted on Oct 27, 2019 at 06:28 PM

    Hi Oliver,

    If there is some logic in how the field is constructed, for instance first or last positions text/numeric, you can use some of the functions available combined with calculated columns, maybe even in multiple steps (calculated columns). You can also create an improvement request via https://influence.sap.com for any missing use cases.

    Kind regards,

    Martijn van Foeken | Interdobs

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.