topic Re: Convert numeric base90 to base10 in QlikView

Convert numeric base90 to base10

Thu, 28 Jul 2011 20:29:31 GMT

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.

Re: Convert numeric base90 to base10

johnw — Thu, 28 Jul 2011 20:48:46 GMT

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

Convert numeric base90 to base10

Mon, 01 Aug 2011 15:33:50 GMT

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

Re: Convert numeric base90 to base10

johnw — Mon, 01 Aug 2011 16:44:02 GMT

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.