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.
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.
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.
