Skip to main content
Announcements
UPGRADE ADVISORY for Qlik Replicate 2024.5: Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
acope307
Contributor II
Contributor II

Global Transformations: Numeric to Interval

We are moving data from Oracle to CSV in ADLS Gen 2.   

The ID fields in one database are defined as NUMBER (or Float), so the resultant CSV is:  27765.0000000000.

In other DB's the same identifier is NUMBER(15,0).  I have tried using global transformations to no avail.  Is there anything that I can do remove the decimals in Global Transformations so I don't have to cleans it on the back end?

 

Qlik Replicate 

Labels (2)
1 Solution

Accepted Solutions
john_wang
Support
Support

Hello Neil,

We are glad to help always.

If you want to set all *_ID columns to NUMERIC(38,0) (but still keep some columns the extra precision), you may use Global Rules Data Type convert rule. You can set specific columns , or the columns which name meet a given pattern in the rule. The below sample shows how to convert the columns %_ID (the column name suffixed by "_ID")  to NUMERIC(38,0) .

john_wang_0-1636688051399.pngjohn_wang_1-1636688063668.png

 

Hope this helps.

Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

View solution in original post

4 Replies
john_wang
Support
Support

Hello @acope307 ,

In Oracle database, there is a datatype NUMBER (without the explicit precision and scale definition), by default Replicate maps it as NUMERIC(38,10), that's why you see additional ten ZERO suffixed. In your sample, you got 27765.0000000000 in CSV file.

We have several options to avoid the unnecessary zeros. Eg using transformation, or adjusting the Oracle source endpoint setting. the latter is the better option. You can change default mapping from NUMERIC(38,10) to NUMERIC(38,0) likes below sample:

john_wang_0-1636027107057.png

Let me know if you need any additional assistance.

Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
acope307
Contributor II
Contributor II
Author

Thanks John!

I didn't see the email from Qlik until this AM...that is the cause of the delay.

Once again...I think you helped me before :)...much appreciated.

Unfortunately, in this case the developer set all *_ID columns to number/float...some columns need the extra precision, I was hoping to be able just do *_ID columns.  The reason is in another system, the *_ID columns are integer data types.  It seems like, I have to wait to get it into my data lake to do the actual cleansing.

Neil

john_wang
Support
Support

Hello Neil,

We are glad to help always.

If you want to set all *_ID columns to NUMERIC(38,0) (but still keep some columns the extra precision), you may use Global Rules Data Type convert rule. You can set specific columns , or the columns which name meet a given pattern in the rule. The below sample shows how to convert the columns %_ID (the column name suffixed by "_ID")  to NUMERIC(38,0) .

john_wang_0-1636688051399.pngjohn_wang_1-1636688063668.png

 

Hope this helps.

Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
acope307
Contributor II
Contributor II
Author

Thanks John...I appreciate it:)