Formatting Numbers to always be 12 digits

[#1]

I have a field in my feed which varies in length between 8 and 16 digits.

I want to create a new field based on this field which is always 12 digits in length.

Thus 12345678 would become 123456780000

1020304050607080 would become 102030405060

There seems to be various possibilities: If/elseif, round, $(#variablename) etc.

I am loading millions of records per day.

What would be the most efficient way of doing this?

Many Thanks

Sandy

[prieper]

You should use the NUM-function, like below

LOAD
 NUM(F1, '000000000000') AS MyNum
INLINE [F1
 12345678
 1234567890
 123458
 123456789012];

sorry, you wanted to have the '0' at the end, have a look into REPEAT:

LOAD
 *,
 Truncated & REPEAT('0', 12 - LEN(Truncated)) AS Format12;
LOAD
 LEFT(x, 12) AS Truncated
INLINE [x
 1020304050607080
 12345678
 1234567890
 123458
 123456789012];

HTH
Peter

HTH
Peter

[#3]

Hi Peter,

First: Num only works to 14 digits.

Second: Sandy want to have 0 behind the numbers not in front of them.

Best wishes

Rainer

[#4]

Hi Peter,

thanks for your suggestions.

I have used the following with success although it does not work with the 16 digit numbers.

load
*,
round(expr*pow(10,12-len(expr))) as expr_key
from ...

I did try and embed a left(expr,12) in the above without success:

expr*pow(10,12-len(left(expr,12)) as expr_key

I am not sure I totally follow your repeat suggestion. Given I want to convert all expr fields to expr_key how would your syntax actually look?

Many Thanks

Sandy

[prieper]

Hi Sandy,

there were quite some threads recently that QV is not able to handle numbers with more than 12(?) digits - just seach this forum. I have splitted the calculation into two for better understanding, but you can combine both lines into one.

Peter

[johnw]

Peter Rieper wrote:there were quite some threads recently that QV is not able to handle numbers with more than 12(?) digits - just seach this forum. I have splitted the calculation into two for better understanding, but you can combine both lines into one.

Hmmm. I thought QlikView was using IEEE double-precision floating point as its internal format. That gives you a 1 bit sign, 11 bit exponent, and 52 binary digits. 2^53 = 9,007,199,254,740,992, so you should have 15 to 16 decimal digits available.

Unfortunately, testing seems to indicate that there is a 14 decimal digit maximum. I'm not sure why that would be. In any case, 12 digits should be fine.

On further testing, I'm going to have to declare this a bug, and I'll be sending it to support for comment. The 14 digit maximum applies even if I'm entering a binary number, such as 11,111,111,111,111. Even though there should be PLENTY of bits available to store more digits, QlikView returns null if I try to specify them. On the other hand, I can enter hexidecimal FFF,FFF,FFF,FFF and QlikView correctly interprets that as 281,474,976,710,655, which is a 15 digit decimal number. If I try to add one more F, it truncates the low order decimal digit and shifts the other digits over WITHOUT adding an exponent, which is simply incorrect. So I still believe I'm right about it using IEEE double-precision floating point, but there's some code somewhere that is being too restrictive on what it allows you to specify, and that breaks down in certain cases for how it interprets your number. Disappointing.

[johnw]

On further review, I don't think I'll be reporting this as a bug.

It appears to me that QlikView has implemented some inconsistent limits on numbers, and that these limits are self-imposed and below the true limit of the IEEE double-precision floating point that is being used. I would call this sloppy coding, but I'm not sure it really rises to the level of being a bug, since they could easily just publish all of the inconsistent rules being used, and accurately claim "working as designed".

As examples of the inconsistencies, refer to the chart below. When I read in the number as hex and display it as binary, the maximum appears to be 50 binary digits, or 3FFFFFFFFFFFF. That would indicate a maximum decimal number of 112,589,990,684,262. However, I can actually read in decimal 999,999,999,999,999 - if and only if I use num(). If I use num#(), it returns null. But the num#() function will work fine with 14 decimal digits. Neither works with 16 decimal digits, even when the decimal number is small enough to fit in the 52 bits theoretically allowed. Even with binary, the num#() function will only allow 14 digits, yet with hex, it allows more than that, and simply doesn't return the right result.

[#8]

Thanks for the detailed analysis John. Here is Qlikviews response on a separate thread:

>>>>
Things are working as designed.

QlikView only supports numeric values up to 14 digits. After this point the value gets rounded off. This is similar to Excel where 123456789123456789 becomes 123456789123456000.

To work around this, you could either load the values as Text or split the field up into 2 or more fields which should reduce the size of the document as well since splitting the field in two will limit the number of distinct values in the fields.

If we take the value 123456789987654321 for example we could load the first 9 digits in one field and the last 9 digits in another field:

F1 - 123456789
F2 - 987654321

These can then be presented in a 'listbox' using =F1&F2.
<<<<