topic Re: Problem interpreting 16 digit Hex key. in QlikView

Problem interpreting 16 digit Hex key.

robinrouleur — Tue, 14 Oct 2014 06:36:58 GMT

Can anyone suggest how to work with a key that is a 16 digit HEX number? I don't need to do any math with it, just use it as a key. My problem is that if I load it as a text string it doesn't sort correctly in charts and tables. In Excel, alphanumeric hex strings sort in numerical hex order but in QV they don't appear to! Can anyone clarify how QV sorts alphanumeric strings?

As an alternative I tried to interpret it as a hex number at load time using Num#(HexKey,'(hex)') AS HexKey. It didn't cause any errors but something was not right as my straight table that uses dimensions from two tables linked by this key generated radically different results.

I read elsewhere in this Community that QlikView cannot interpret strings longer than 14 digits and suspect that this might be the cause. Can anyone confirm exactly what the limit is for the latest version of QV11?

I did a bit of testing using:

Num#(Right(HexKey,14),'(hex)') AS HexKey,

And discovered that I had to go down to 13 to make it work.

I'm happy with string or hex interpretation, I just need it to work reliably as a key and sort in numerical hex order. Any advice would be much appreciated.

Thanks in advance

Robin

Re: Problem interpreting 16 digit Hex key.

jonathandienst — Tue, 14 Oct 2014 06:43:57 GMT

I have no problem reading a 16 digit hex number using

Num#(HexVal, '(hex)') As T1

See attached. QV11.2 SR7

HTH

Jonathan

Re: Problem interpreting 16 digit Hex key.

robinrouleur — Tue, 14 Oct 2014 09:27:28 GMT

Thanks Jonathan

Num#(HexVal,'(hex)') seems to be unpredictable when the number of hex digits approaches 16, for some it works and for some it doesn't. I put different numbers into your inline load statement and managed to repeat my problem.

In my testing the Num# output typically has the same first 13 hex digits as the source but some or all of the last (least significant) three are different. I can't see how to attach my modified qvw to this reply (can you advise?) so here is the load statement. With this data your "Num# (hex)" list box only has 3 values, those ending in 2b, 30 and f0. The other numbers from the source were translated by Num# into one of these.

LOAD
*,
Num#(HexVal, '(hex)') As T1
Inline
[
HexVal
006f024ff64d3a2b
006f024ff64d3a2c
006f024ff64d3a2d
006f024ff64d3a30
006f024ff64d3a31
fffffffffffffff0
fffffffffffffff1
fffffffffffffff2
fffffffffffffff3
fffffffffffffff4
fffffffffffffff5
fffffffffffffff6
fffffffffffffff7
fffffffffffffff8
fffffffffffffff9
fffffffffffffffa
fffffffffffffffb
fffffffffffffffc
];

Re: Problem interpreting 16 digit Hex key.

robinrouleur — Wed, 15 Oct 2014 04:08:51 GMT

After further testing I have come to two conclusions:

1. A -> Z sorting of strings appears to have a bug(feature?) where a substring "0m" sorts after "n" for m>n.

eg: the following strings sort in the order shown:

f1f

f02

f2a

f03

When they should sort: f02, f03, f1f, f2a.

2. Num#(HexVal,'(hex)') is limited by the storage used for integers which appears to be 64 bits. A 16 digit hex number = 64 bits + sign etc so the limit, if 64 bit integer storage is used, would be less than 16 digits.