hextonumber not working with 64bits

[#1]

Hi 

I have hex that is bea000000004080bh, I tried hextonumber("bea000000004080bh"), but it is not working. Can someone help me?

[#2]

Hi,

Are you sure it is a 64-bit Hexadecimal? Including the "h" digit does not seem to conform with the standard format.

Best,

TS

[#3]

sorry, forgot to remove the h at the end. the output raw always have h at the end to indicate hex value which we need to exclude before we do hextonumber. 

Now the issue is it doesnt match to the number as what we expected.
JMP converted it as -4.768372e-7

expected is -4710765210229274613

What I m missing?

[#4]

The value you show is signed integer and HexToNumber() returns unsigned integer.

Also you need to specify the base, something like 

a = HexToNumber("bea000000004080b",Base(16));

To get around the signed integer issue, you can use a formula like this:

max = HexToNumber("7fffffffffffffff",Base(16));
a = HexToNumber("bea000000004080b",Base(16));

if( a > max,
	result = -(max+1) + (a-max);
, // else
	result = a;
);

But then you run into another issue which is JMP cannot render a 64-bit integer value precisely.  The last two digits will get truncated and have random values.  I would recommend breaking up your 64-bit value into a upper 32-bit value, and a lower 32-bit value, and store those values in two separate columns.  Something like this:

Upper32 = HexToNumber( Left( :hex64, 8 ), Base( 16 ));
Lower32 = HexToNumber( Right( :hex64, 8 ), Base( 16 ));

Refer to the attached data table example.

[#5]

sounds like there is improvement needed for the hextonumber() why cant it do the same like the calculator do (see picture in very first post)?

breaking down upper and lower 32 is not efficient when dealing with millions of data points. I have to process hundred of files that each contains more than 40M rows of hex value. doing the breaking down 32 will consume too much time.

any other suggestion?

[#6]

I would use python for this.  The good news is JMP18 can run python scripts directly in JMP.  This would be the python script to do what you want:

import binascii

h = 'bea000000004080b'
b = bytes(h, 'utf-8')
ba = binascii.a2b_hex(b)
print(int.from_bytes(ba, byteorder='big', signed=True))

As far as why JMP is limited to 17 digits of precision, that is because JMP is using float64 for numerical values.  To get more than 17 digits, JMP would need to be recompiled using float128 for numerical values, causing it to use more system memory.  If this is important, you can submit it as a feature request.  You can do that here: https://community.jmp.com/t5/JMP-Wish-List/idb-p/jmp-wish-list

[#7]

@dadawasozo what is too much time (you have over 40 000 000 * 100 rows of data)? If you have to convert that data fast, you might have to get a bit creative (not directly related to this but interesting to read about "The One Billion Row Challenge", https://github.com/gunnarmorling/1brc, you can also find solutions for other languages from google and there are also videos in YouTube which show and explain some solutions).

@Mark_Zwald When you go back to JMP is there any way of avoiding "OverflowError: Python int too large to convert to C long" outside of converting to string? Splitting the value?

Names Default To Here(1);

hex_to_signed_int_str = function({h},
	Python Execute({h}, {x},
"\[import binascii

b = bytes(h, 'utf-8')
ba = binascii.a2b_hex(b)
x = int.from_bytes(ba, byteorder='big', signed=True)
#x = str(x)
]\");
	return(x);
);

signed_int_str = hex_to_signed_int_str("bea000000004080b");

show(signed_int_str);

[#8]

@jthi splitting the value or storing as a string would be the only way to preserve all the digits.  Any numerical value in JMP will be limited to 17digits due to the limitations of float64 variables in C++.  Apparently Python has unlimited precision in the sense that numerical values aren't limited to 64bits or 128bits, but can use as many bits as needed to store the value as long as you have the RAM to support it.

[#9]

yes, we integrate python for that.

Thanks everyone for the input.