-
How to convert date format from strange to normal
Jason Michaelides Apr 12, 2012 5:35 PM (in response to Fei Xu )Can you post a sample of your source data? And exactly where on the server are you getting the data from?
-
Re: How to convert date format from strange to normal
Fei Xu Apr 12, 2012 5:49 PM (in response to Jason Michaelides )I got the data from CalData.pgo.xml (Because I have set PGOAsXmlAlso=1 in settings.ini, I can have CalData.pgo in xml file). It is located in server document folder. Sample is attached. Thanks a lot.
-
Sample.qvw 173.0 K
-
Re: How to convert date format from strange to normal
Jason Michaelides Apr 12, 2012 6:33 PM (in response to Fei Xu )Can you post the xml file too?
-
Re: How to convert date format from strange to normal
Fei Xu Apr 12, 2012 7:04 PM (in response to Jason Michaelides )Thank you for your time!
-
CalData.pgo.xml 1.8 K
-
Re: How to convert date format from strange to normal
Jason Michaelides Apr 13, 2012 4:48 AM (in response to Fei Xu )I don't think I can be much help to be honest! I can only guess that those strings are links to another record, mabe in another of the .pgo files.
Sorry mate,
Jason
-
How to convert date format from strange to normal
Stefan Bäckstrand May 7, 2012 5:00 AM (in response to Jason Michaelides )It's a UNIX timestamp in hexadecimal.
Conversion:
40E40633h = 1088685619
Excel formula (for example):
=(1088685619)/60/60/24+DATE(1970,1,1)
Result:
Thursday, 2004-07-01 14:40:19
-
How to convert date format from strange to normal
Fei Xu May 7, 2012 5:01 PM (in response to Stefan Bäckstrand )Thank you so much, Stefan!
How can I add any lines to QlikView Scripts to convert UNIX timestamp in hexadecimal to default date/time format? Is it possible?
Fei
-
How to convert date format from strange to normal
Steve Dark May 7, 2012 6:07 PM (in response to Fei Xu )Building on Stefan's post, I presume the QlikView code would be:
Date((1088685619/60/60/24)+Date#('19700101', 'YYYYMMDD'), 'DD MMM YYYY') as [My Date Field],
The second date format specifier you can change to be whatever you want, but the first one must match the format of the 1970 date.
Hope that helps,
Steve
http://www.quickintelligence.co.uk/
-
How to convert date format from strange to normal
Fei Xu May 7, 2012 6:19 PM (in response to Steve Dark )Steve,
Thank you very much!
The last thing is the date/time I get from CalData.pgo.xml looks like 40E4067AD8153D10, 40E406245C71C71C, FE37E43C8800759C. How can I convert them to like 1088685619 ? Any way to do it in script?
-
How to convert date format from strange to normal
Fei Xu May 7, 2012 7:18 PM (in response to Steve Dark )For example, the Lastupdated for a particular document is 40E4099EDEE8DD7D. I am pretty sure it is in this year 2012. However no matter how I tried to convert 40E4099EDEE8DD7D to Decimal number and calculate the date, the result is not in 2012.
-
How to convert date format from strange to normal
Rob Wunderlich May 7, 2012 7:46 PM (in response to Fei Xu )Updating Steve's example:
=Date((num#('40E4099E', '(HEX)')/86400)+Date#('19700101', 'YYYYMMDD'), 'DD MMM YYYY')
-Rob
-
Re: How to convert date format from strange to normal
Fei Xu May 7, 2012 7:53 PM (in response to Rob Wunderlich )Thank you very much Rob but the result is 2004, not 2012.
-
How to convert date format from strange to normal
Rob Wunderlich May 7, 2012 8:58 PM (in response to Fei Xu )I can't comment on your expectations of the source data. But I believe the conversion is "correct". Today's date in hex unix timestamp is 4FA85E73.
-Rob
-
Re: How to convert date format from strange to normal
Fei Xu May 7, 2012 10:49 PM (in response to Rob Wunderlich )Thank you very much Rob!
-
How to convert date format from strange to normal
Steve Dark May 8, 2012 6:17 AM (in response to Fei Xu )Sometimes application's timestamps are not actually representations of the time - just a unique string that has no value other than being a unique string.
It should be possible to find out if that is the case for this file.
- Steve
-
-
-
-
-
-
-
-
Re: How to convert date format from strange to normal
Fei Xu May 7, 2012 7:47 PM (in response to Stefan Bäckstrand )Maybe they are not UNIX timestamp in hexadecimal. I couldn't use any converter to convert them to dec and then add to qlikview sciprts
40E4067AD8153D10,
40E406245C71C71C,
FE37E43C8800759C.
For example through this website:
-
Re: How to convert date format from strange to normal
Mark Shannon Nov 18, 2016 11:21 AM (in response to Fei Xu )For anyone needing to convert these dates in SQL Server, below is a formula to calculate the date portion. Just replace c.LastUsed with your date field. You'll notice the base date is not the UNIX base date of 1/1/1970 but Microsoft's base date. c.LastUsed needs to be a Binary(8) field
DATEADD(day, 1e0 * (CAST(SUBSTRING(c.LastUsed, 6, 3) AS INT) / 2097152e0 / 536870912e0 + (SUBSTRING(c.LastUsed, 2, 4)&268435455) / 536870912e0 + 0.5e0) * POWER(2e0, (SUBSTRING(c.LastUsed, 1, 2)&32752) / 16 - 1022), '1899-12-30')
-
-
-
-
-
-
-
-
Re: How to convert date format from strange to normal
René Verschuren Jul 5, 2013 7:58 PM (in response to Fei Xu )You can convert these strange dates (actually the dates are 64 bit hexadecimals) by using a formula I posted in http://community.qlik.com/docs/DOC-4388.
René
-
Re: How to convert date format from strange to normal
Fei Xu Jul 7, 2013 4:56 PM (in response to René Verschuren)Thank you very much Rene.
-
Re: How to convert date format from strange to normal
Mikael Kristensen Mar 5, 2015 4:00 AM (in response to Fei Xu )Hi, did you manage to convert the number 'FE37E43C8800759C' using the link René posted? I cannot make it work...
-
Re: How to convert date format from strange to normal
René Verschuren Mar 5, 2015 5:48 AM (in response to Mikael Kristensen)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é
-
Re: How to convert date format from strange to normal
Mikael Kristensen Mar 12, 2015 9:39 AM (in response to René Verschuren)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?
-
Re: How to convert date format from strange to normal
René Verschuren Mar 12, 2015 11:24 AM (in response to Mikael Kristensen)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.
-
Re: How to convert date format from strange to normal
Mikael Kristensen Mar 13, 2015 4:40 AM (in response to René Verschuren)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?
-
Re: How to convert date format from strange to normal
René Verschuren Mar 13, 2015 5:45 AM (in response to Mikael Kristensen)Please post your QVW file.
-
Re: How to convert date format from strange to normal
Mikael Kristensen Mar 13, 2015 8:19 AM (in response to René Verschuren)sorry, confidential data.
-
Re: How to convert date format from strange to normal
René Verschuren Mar 13, 2015 8:32 AM (in response to Mikael Kristensen)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.
-
Re: How to convert date format from strange to normal
Mikael Kristensen Mar 16, 2015 5:03 AM (in response to René Verschuren)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?
-
Re: How to convert date format from strange to normal
René Verschuren Mar 16, 2015 7:10 AM (in response to Mikael Kristensen)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é
-
Re: How to convert date format from strange to normal
Mikael Kristensen Mar 16, 2015 7:46 AM (in response to René Verschuren)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.
-
-
-
-
-
-
-
-
-
-
-
-
Re: How to convert date format from strange to normal
Mark Shannon Nov 18, 2016 4:28 PM (in response to René Verschuren)Alternatively to Rene's formula, the below appears to work just as well but is much simpler:
if(Mid(LastUsed,1,2) < '80',
date((Num(NUM#(Mid(LastUsed,11,6),'(HEX)'),'(BIN)')/2097152/536870912+(Num(NUM#(Mid(LastUsed,3,8),'(HEX)'),'(BIN)') bitand 268435455)/536870912+0.5)*pow(2,(num(num(Num#(MID(LastUsed,1,4),'(HEX)'),'(bin)')) bitand 32752) /16-1022))
, '')as LastUsed
-