Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
One method could be -
Load subfield(Value,'0',-1) inline [
Value
000006621
8821
8956
000009764
0000A25R6
R738G
A23B5
00000TSK87 ];
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
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
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.