Altair RISE

A program to recognize and reward our most engaged community members

Nominate Yourself Now!

How to not have numbers convert to Scientific Notation when inserted into DB by Automator

Hello,

In DPS is there a way to make sure that numbers that are displayed normally don't get converted to scientific notation when inserted into a database by Automator. Currently, the only way I found is to convert them to Text, which is not desirable.

#Automator #DPS #scientificnotation

------------------------------
Leonid Shteyngardt
App Development Manager
Canaccord Genuity Inc.
New York NY
(212) 389-8176
------------------------------

Find more posts tagged with

English

Monarch

Accepted answers

All comments

Mo Abdolrahim

Hi Leonid,

You can create the table schema in the SQL database table and configure columns (fields) based on their types.

For example, numeric integer fields to Int, or Numeric decimal fields to Float.

In the Automator Visual Process in Data Prep Export you can manually map each workspace table field to a table field in SQL.
Regards
Mo

------------------------------
Mahmoud Abdolrahim
Senior Implementation & Integration Engineer
Altair Engineering
------------------------------
-------------------------------------------
Original Message:
Sent: 12-03-2020 11:27 AM
From: Leonid Shteyngardt
Subject: How to not have numbers convert to Scientific Notation when inserted into DB by Automator

Hello,

#Automator #DPS #scientificnotation

------------------------------
Leonid Shteyngardt
App Development Manager
Canaccord Genuity Inc.
New York NY
(212) 389-8176
------------------------------

Leonid

Hi Mo,

We'd like to keep all columns in the tables that we load from Monarch as varchar, in case the input data is not perfect - alpha characters in numeric or date fields, etc. This way we at least load the data and then deal with cleanup in stored procedures.
So, although DPS displays the large numbers correctly as they are in the PDF file - all digits visible, when we run the process in Automator, it converts large (or small) numbers into scientific notation and thus losing information - 123456789 gets converted into 1E+8. To avoid this, we have to convert each field into Character - either in the Report Design or later in the table.
It would be helpful if Monarch would not implicitly convert numbers to scientific notation when inserting into varchar columns.

Thank you,
Leonid

The information contained in this communication may be confidential and/or legally privileged. If you are not the intended recipient, please contact the sender and destroy all copies of this message and any attachments. Any disclosure, copying, distribution or taking any action in reliance on this information other than by the intended recipient is strictly prohibited and may be unlawful. Canaccord Genuity LLC is required by regulation to review and store both outgoing and incoming electronic correspondence. E-mail transmissions cannot be guaranteed to be secure, timely or error-free. This communication is not an offer or solicitation to buy or sell any security or other investment product. Additional information, including disclosures regarding securities under research coverage, is available at http://www.canaccordgenuity.com/en/Our-Company/Research/. Any information regarding specific investment products is subject to change without notice. Canaccord Genuity LLC - Member FINRA/SIPC.

-------------------------------------------
Original Message:
Sent: 12/3/2020 11:28:00 AM
From: Leonid Shteyngardt
Subject: How to not have numbers convert to Scientific Notation when inserted into DB by Automator

Hello,

#Automator #DPS #scientificnotation

------------------------------
Leonid Shteyngardt
App Development Manager
Canaccord Genuity Inc.
New York NY
(212) 389-8176
------------------------------

Phil_20586

Hi Leonid,
You can create the table schema in the SQL database table and configure columns (fields) based on their types.
For example, numeric integer fields to Int, or Numeric decimal fields to Float.
In the Automator Visual Process in Data Prep Export you can manually map each workspace table field to a table field in SQL.
Regards
Mo
------------------------------
Mahmoud Abdolrahim
Senior Implementation & Integration Engineer
Altair Engineering
------------------------------
-------------------------------------------
Original Message:
Sent: 12-03-2020 11:27 AM
From: Leonid Shteyngardt
Subject: How to not have numbers convert to Scientific Notation when inserted into DB by Automator

Hello,
In DPS is there a way to make sure that numbers that are displayed normally don't get converted to scientific notation when inserted into a database by Automator. Currently, the only way I found is to convert them to Text, which is not desirable.

#Automator #DPS #scientificnotation

------------------------------
Leonid Shteyngardt
App Development Manager
Canaccord Genuity Inc.
New York NY
(212) 389-8176
------------------------------

Leonid,

For financial values (for example) as Mo said I use the decimal feature when writing to the database. However, if you read that DB field back into DPS and then perform add/sum/difference functions I have experienced where fractional parts of a penny still result from calculation reading in DB fields that had more decimal precision. The two methods I have used to overcome this are: a) convert to text and then back to a number in DPS or b) use the round function within mathematical arguments.

Phil Magrogan

Immersion - SDVOSB

-------------------------------------------
Original Message:
Sent: 12/3/2020 11:48:00 AM
From: Mahmoud Abdolrahim
Subject: RE: How to not have numbers convert to Scientific Notation when inserted into DB by Automator

Hi Leonid,

You can create the table schema in the SQL database table and configure columns (fields) based on their types.

For example, numeric integer fields to Int, or Numeric decimal fields to Float.

In the Automator Visual Process in Data Prep Export you can manually map each workspace table field to a table field in SQL.
Regards
Mo

#Automator #DPS #scientificnotation

------------------------------
Leonid Shteyngardt
App Development Manager
Canaccord Genuity Inc.
New York NY
(212) 389-8176
------------------------------