Qlik Community

QlikView Documents

Documents for QlikView related information.

Converting 64 bit hexadecimal (PGO dates) to decimal

rverschuren
New Contributor II

Converting 64 bit hexadecimal (PGO dates) to decimal

Hi,

Dates (like the LastUsed date) in the CalData.pgo.xml file are saved as 64 bit hexadecimal numbers.

Those numbers can be converted to decimals by using the double precision floating point format (IEEE-754 standard).

And if you have a decimal number, you can format the number as a date.

I wrote a very powerful formula for this type of conversion.

You can use this formula anywhere in your QlikView document, because it is just some code in a variable.

Just add a new variable with the name eConvertHexToDec and paste in the formula from the attached text file.

After adding the formula to your document, you can use it like any other variable.

One thing, you must use dollar sign expansion.

So, this is the right way: $(eConvertHexToDec(LastUsed))

If you want to test it with the Pi number in a textbox, you must enter this in the text field:

=$(eConvertHexToDec('400921FB54442D18'))

And the result should be 3.1415926535898.

For an example look at the attached QlikView document.

One more thing, the hexadecimal number must always be 16 characters long.

Have fun with it.

Kind regards,

René Verschuren

The Netherlands

Labels (1)
Attachments
Comments
xufei123
Valued Contributor

Thank you very much Rene.

0 Likes
Not applicable

Great....Works fine........

0 Likes
jschrader
New Contributor III

Thank you very much. The code worked like a charm!

0 Likes
Employee
Employee

Articles like this are the reason I love the community. This multi-lines long formula is also required to interpret many number values in the XML project structure. I've made a nice bookmark report out of this, which reports all bookmark settings (including Fields and values).

0 Likes
maleksafa
Valued Contributor

Hi

i am having some troubles working with this variable maybe i am missing something.

i have created a new variable called Convert64bit_HEXtoDEC and pasted the code Convert64bit_HEXtoDEC .txt in it, but it is generating errors with bad field names $1.

appreciate your help.

0 Likes
jschrader
New Contributor III

The same thing happened to me when I put the code in a variable. I do not know why this happens, but the variable should work when using it.

0 Likes
MVP & Luminary
MVP & Luminary

How do you use the variable? It works for me. The parameter needs to be a text..

0 Likes
maleksafa
Valued Contributor

can someone please attach a sample working qvw with the variable and a working text object?

0 Likes
MVP & Luminary
MVP & Luminary

Cannot attached a document here. But I've tried at least the Pi example which worked in v11.2 SR5:

=$(Convert64bit_HEXtoDEC('400921FB54442D18'))

0 Likes
MVP & Luminary
MVP & Luminary

Here is a example: License_Analyser_K3FDS.qvw

- Marcus

Version history
Revision #:
1 of 1
Last update:
‎06-26-2013 07:57 PM
Updated by: