Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
NZFei
Partner - Specialist
Partner - Specialist

How to convert date format from strange to normal

Hello, I have created a qvw file to show qlikview server information. I have ServerName, Name, NoOfNamedCALs and so on. However for three fields LastUpdated,  LastUsed and ToBeDeleted, the value (should be date, aren't they?) are strange. They are 40E4067AD8153D10, 40E406245C71C71C, FE37E43C8800759C. I have tried to set the number formats for them to Date but it didn't work. Are they some kinds of date formats? How can I convert them to normal formats? Many thanks in advance!

31 Replies
rverschuren
Contributor II
Contributor II

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

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
Contributor II
Contributor II

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

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
Contributor II
Contributor II

Please post your QVW file.

Not applicable

sorry, confidential data. 

rverschuren
Contributor II
Contributor II

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

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?     

rverschuren
Contributor II
Contributor II

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

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.