Turn text to number during OData data ingestion

[oliver_mainka]

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/eac3111c11174776856d9c1fb...), 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?

[mfoeken]

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

[mfoeken]

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

[oliver_mainka]

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.

[mfoeken]

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

[oliver_mainka]

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