topic Re: Convert datatypes in Oracle Table in QlikView

Convert datatypes in Oracle Table

kalyandg — Tue, 02 May 2017 01:56:35 GMT

Hi All,

I have a requirement to convert the datatype of a particular field to link properly. Table is from Oracle database.

i have a values like, Integer, AlphaNumeric and Char.

Int:

000006621

8821

8956

000009764

I am having integer values like the above, i don't want the leading zeroes, i need only numbers.

AlphaNumeric

0000A25R6

R738G

A23B5

00000TSK87

in this Alpha Numeric values, i dont need leading zeros,

Which method I can use it to delete leading zeroes ? Please help.

Why I am going to delete leading zeroes is, in Database it was not there, but in Qlikview it is showing.

Thanks,

Kalyan

Re: Convert datatypes in Oracle Table

Digvijay_Singh — Tue, 02 May 2017 02:52:30 GMT

One method could be -

Load subfield(Value,'0',-1) inline [

Value

000006621

8821

8956

000009764

0000A25R6

R738G

A23B5

00000TSK87 ];

Re: Convert datatypes in Oracle Table

Anil_Babu_Samineni — Tue, 02 May 2017 04:31:13 GMT

Can you provide Real data, How data looks??

What are you expecting from below data. Because, I am assuming there are many possibilities with me.

000006621

8821

8956

000009764

0000A25R6

R738G

A23B5

00000TSK87

0000A250R6

Re: Convert datatypes in Oracle Table

rahulpawarb — Tue, 02 May 2017 05:16:46 GMT

Hello Kalyana,

Trust that you are doing well!

You can make use of below sample script:

Data:
LOAD Num(Int) AS Int,
            PurgeChar(Int, '0') AS NewInt,
          PurgeChar(AlphaNumeric, '0') AS AlphaNumeric;
LOAD * INLINE [
Int, AlphaNumeric
000006621, 0000A25R6
88210, R738G0
89560, A23B50
000009764, 00000TSK87
];

This will work absolutely fine for Numbers; whereas PurgeChar function will not work when there will be zero's in between of given field value. BTW, I am working on to get the 100% correct solution. Soon I will update you on this.

Hope this will be helpful.

Regards!

Rahul

Re: Convert datatypes in Oracle Table

jonathandienst — Tue, 02 May 2017 06:01:10 GMT

Either do the cleaning in Oracle, or do something like this:

LOAD *,

If(IsNum(Field), Num(Field), Replace(Trim(Replace(Field, '0', ' ') & '|'), ' ', '0')) as ResultField

;

SQL ....

Explanation:

Num() will strip the leading zeroes from the numreric values.

For the alpha values, replace the 0's with spaces, add a 'fence' to prevent removing trailing zeroes, trim, replace the spaces with zeroes again. This assumes that there are no spaces inthe source field.