topic Global Transformations: Numeric to Interval in Qlik Replicate

Global Transformations: Numeric to Interval

acope307 — Wed, 03 Nov 2021 20:32:45 GMT

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?

Re: Global Transformations: Numeric to Interval

john_wang — Thu, 04 Nov 2021 12:02:02 GMT

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:

Let me know if you need any additional assistance.

Regards,

John.

Re: Global Transformations: Numeric to Interval

acope307 — Thu, 11 Nov 2021 14:15:11 GMT

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

Re: Global Transformations: Numeric to Interval

john_wang — Fri, 12 Nov 2021 03:36:44 GMT

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) .

Hope this helps.

Regards,

John.

Re: Global Transformations: Numeric to Interval

acope307 — Fri, 12 Nov 2021 21:43:10 GMT

Thanks John...I appreciate it:)