Qlik Community

QlikView Management

Discussion Board for collaboration on QlikView Management.

rverschuren
New Contributor II

Re: How to convert date format from strange to normal

Hi Mikael,

The most accurate decimal representation of hex value FE37E43C8800759C is -1.0000000000000000525047602552E300.

This is a negative 1 with really a lot of zeros.

Hex value FE37E43C8800759C is not a date.

Enormously big values cannot be used in QlikView.

http://www.binaryconvert.com/result_double.html?hexadecimal=FE37E43C8800759C

Use the formula from post http://community.qlik.com/docs/DOC-4388 to convert hex values to decimals.

Past the formula (look at file Convert64bit_HEXtoDEC.txt) into a variable.

Use the formula in your script or dashboard in this way: $(eConvertHexToDec(LastUsed))

LastUsed is a field containing a date as a hex value.

Hex value 40E4570D1ABCDF01 equals to decimal 41656,409513889.

This decimal represents date 2014-01-17 09:49:42 (YYYY-MM-DD HH:MM:SS).

Open the QVW (Convert_double_precision_floating_point_HEX_to_DEC.qvw) for a working example.

Best regards,

René

Not applicable

Re: How to convert date format from strange to normal

Thx, it works great. Though I have one question:

When I make a straight tabel with calculated dim using the converter and for expression a field like Documents, then I get all the documents, and that is all fine. On the other hand if switch around and use the field as dim and put the converter in the expression then something goes wrong I dont get all the documents. How can that be?

rverschuren
New Contributor II

Re: How to convert date format from strange to normal

Your problem might be caused be NULL values.

Try to convert NULL values to a default date with use of the Alt function.

For example:

=Date (Alt ($(eConvertHexToDec(LastUsed)), 2))

Value 2 is date 1-1-1900.

When you calculate expressions on-the-fly, the performance could be bad.

Put the conversion from hex date to decimal date into the loading script.

Not applicable

Re: How to convert date format from strange to normal

thx it worked, but it did not really solve the issue. That is I get all the "-" shown with this date. Still I only have a few documents with the real date and the rest with dates is still not shown.

The strange thing is if have both tables described above, and then select in the table where all is shown, then suddenly it also appear in the other table where it initial is not shown. Any good explanation for this?

rverschuren
New Contributor II

Re: How to convert date format from strange to normal

Please post your QVW file.

Not applicable

Re: How to convert date format from strange to normal

sorry, confidential data. 

rverschuren
New Contributor II

Re: How to convert date format from strange to normal

Try to convert the hex dates with help of an online converter, for example: http://www.binaryconvert.com/result_double.html?hexadecimal=FE37E43C8800759C

I think the hex dates causing your problem are not dates.

Dates are decimals between 0 and say 50000.

"-" means NULL value.

Not applicable

Re: How to convert date format from strange to normal

hmm, that should not be the problem. When I make the straight table with the dimension calculated using the convert variable, I do get all the dates and it looks fine, I get a long list, they are not "-", the hexadecimal code is solid.

In the same qvw and sheet making another table just with the converter in the expression instead of as an calculated dimension, it only shows a few documents.

The funny thing is that the two table are connected very fine, so if I qlik on the first table with the long list, it actually do show this one in the second table which initial did not show that document and date.

I do not understand why there is this difference in the tables?     

Highlighted
rverschuren
New Contributor II

Re: How to convert date format from strange to normal

Hi Mikael,

I think it is a good idea to do the conversion in the script instead of doing the conversion in a straight table.

Then it is easier to pinpoint the exact reason why you are getting strange results.

Hope this will help.

Best regards,

René

Not applicable

Re: How to convert date format from strange to normal

Thanks, I tried this and for the straight tables it does not change anything whether I calculated it or use a field from the script. Though it enable to make a listbox and then I get all the documents and converted dates including null values for both fields.