Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET:
**REGISTER NOW**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Formatting Numbers to always be 12 digits

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

2009-09-28
12:19 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Formatting Numbers to always be 12 digits

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

5,794 Views

7 Replies

prieper

Master II

2009-09-28
12:43 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Not applicable

2009-09-28
12:54 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Not applicable

2009-09-28
03:48 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

3,677 Views

prieper

Master II

2009-09-28
04:47 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

3,677 Views

johnw

Champion III

2009-09-28
10:33 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

3,677 Views

johnw

Champion III

2009-09-29
12:30 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

3,677 Views

Not applicable

2009-09-29
11:47 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

<<<<

3,677 Views