Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
kalyandg
Partner
Partner

Convert datatypes in Oracle Table

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

4 Replies
Digvijay_Singh
Master III
Master III

One method could be -

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

Value

000006621

8821

8956

000009764

0000A25R6

R738G

A23B5

00000TSK87 ];

Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
rahulpawarb
Specialist III
Specialist III

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

jonathandienst

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein