Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Convert numeric base90 to base10

Hi

I have a legacy system that has numeric data stored in base90 in one of our tables.

I want to extract and convert to base10 (A bit easier to work with !!!!)

Has any one done this before and if so what is the trick.

(Not sure if any relevence but the db that I am pulling from is Visual Foxpro using ODBC driver)

Looking forward to any help.

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Convert numeric base90 to base10

By base 90, you mean that '15' should be converted to 1*90 + 5*1 = 95, and '321' to 3*90*90 + 2*90 + 1 = 24481?

What characters are you using once you get past digits 0-9?  I'm guessing A-Z, but that leaves a whole lot of characters to go.

I'm not sure this is a good way, but you could make a map of your base 90 digits converted to base 10 value:

Base90Map:
LOAD * INLINE [
Base90, Base10
0,0
1,1
2,2
...
9,9
A,10
B,11
...
Z,35
...
];

And then apply the map to each digit of your input field using the appropriate power of 90:

,applymap('Base90Map',mid(Base90Input,1,1))*pow(90,0)
+applymap('Base90Map',mid(Base90Input,2,1))*pow(90,1)
+applymap('Base90Map',mid(Base90Input,3,1))*pow(90,2)
+applymap('Base90Map',mid(Base90Input,4,1))*pow(90,3)
... as Base10Result

3 Replies
MVP
MVP

Re: Convert numeric base90 to base10

By base 90, you mean that '15' should be converted to 1*90 + 5*1 = 95, and '321' to 3*90*90 + 2*90 + 1 = 24481?

What characters are you using once you get past digits 0-9?  I'm guessing A-Z, but that leaves a whole lot of characters to go.

I'm not sure this is a good way, but you could make a map of your base 90 digits converted to base 10 value:

Base90Map:
LOAD * INLINE [
Base90, Base10
0,0
1,1
2,2
...
9,9
A,10
B,11
...
Z,35
...
];

And then apply the map to each digit of your input field using the appropriate power of 90:

,applymap('Base90Map',mid(Base90Input,1,1))*pow(90,0)
+applymap('Base90Map',mid(Base90Input,2,1))*pow(90,1)
+applymap('Base90Map',mid(Base90Input,3,1))*pow(90,2)
+applymap('Base90Map',mid(Base90Input,4,1))*pow(90,3)
... as Base10Result

Not applicable

Convert numeric base90 to base10

The therory was perfect - JW what a star.

Took longer to work out the character set than write the script !!!!!

Only minor issue was the apply map did not work but nothing a left join didn't resolve.

Thanks

MVP
MVP

Re: Convert numeric base90 to base10

It looks like I was missing some closing parens (fixed above).  I'm not sure if that was the problem with the applymap, but left join is probably fine.