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!

1 Solution

Accepted Solutions
rverschuren
Contributor II
Contributor II

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é

View solution in original post

31 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

Can you post a sample of your source data?  And exactly where on the server are you getting the data from?

NZFei
Partner - Specialist
Partner - Specialist
Author

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.

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Can you post the xml file too?

NZFei
Partner - Specialist
Partner - Specialist
Author

Thank you for  your time!

Jason_Michaelides
Luminary Alumni
Luminary Alumni

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

StefanBackstrand
Partner - Specialist
Partner - Specialist

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

http://fmdiff.com/fm/timestamp.html

NZFei
Partner - Specialist
Partner - Specialist
Author

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

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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/


NZFei
Partner - Specialist
Partner - Specialist
Author

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?