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: 
mp802377
Creator II
Creator II

16 digit # (exp:1170410000000000) from DB2 table - convert to time

Hello,

I have this 16 digit number (example: 1170410000000000) from a DB2 table. I need to make it a time. I have searched the forums and tried this: Date(MakeDate(1970,01,01) + YourDate / 24 / 60 / 60). That didn't work. I also tried TIMESTAMP(Timestamp,'DD-MM-YYYY HH:MM:SS') as Date, and that didn't work either. This isn't a hexadecimal. I looked through the data and don't see any letters.

How do I make this a date?

Below is an example of my data. I just pulled this qvd into the Qlikview application. I changed the field names for the most part. I don't have anything else in the file.

           

value onevalue 2value3value 4value 5value 6value7sizeTimestampTMZDIFFWRITETIME
00000000117041000000100001170410000000000
00000000117041000000100001170410000000000
00000000117041000000100001170410000000000
7.977.978.117.978.11564008.111117041000000100001170410000000000
39.9939.99039.99030701117041000000100001170410000000000
180.76180.76178.32180.76178.3211902178.325117041000000100001170410000000000
203.26203.260203.2603207117041000000100001170410000000000
335.31335.310335.31013507117041000000100001170410000000000
599.24599.24592.26599.24592.265271592.265117041000000100001170410000000000


Thank you,

Martha

1 Solution

Accepted Solutions
Anil_Babu_Samineni

IS this, you are expecting?

Timestamp(25569 + Round(Evaluate(Timestamp)/1000000)/86400)


Capture.PNG

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

9 Replies
Anil_Babu_Samineni

Try this

=Timestamp(25569 + Round(WRITETIME/1000) / 86400)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anil_Babu_Samineni

Or this

=Timestamp(25569 + Round(WRITETIME/1000000) / 86400)


It returns as -- 02-02-2007 09:53:20

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
mp802377
Creator II
Creator II
Author

No this didn't work. On both examples I get a - .

Anil_Babu_Samineni

IS this, you are expecting?

Timestamp(25569 + Round(Evaluate(Timestamp)/1000000)/86400)


Capture.PNG

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
mp802377
Creator II
Creator II
Author

That worked. This is interesting. It works in the load script but not as an expression. Thank you so much for your help on this.

Martha

mp802377
Creator II
Creator II
Author

I thought it worked, but then I looked at the date. This data is from last week. The dates should be June, not February.

Any ideas? This is from a DB2 table.

Martha

Anil_Babu_Samineni

Glad i was able to offer you better than earlier

Q) Can you tell us one thing, Whether which date and Year and Time period for this 1170410000001000 And this will make lot better if you know the answer

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
mp802377
Creator II
Creator II
Author

I figured it out. I don't know why the person set the date up this way, be they did. There are a lot of rows and it is minute data. So 1170410000001000  = 04/10/2017 00:00:01. Once I realized he took of the 1 in front of the number it became easier to read. And us storing less than two months of data sounds about right.


Thank you for your help on this. I am sorry it was created this way. I think I have it from here. Again, thank you so much. I hope this didn't waste too much of your time.


Martha

Anil_Babu_Samineni

Hmmm. I thought QlikView has IEEE double-precision floating point as its internal format. That gives you a 1 bit sign, 11 bit exponent, and 52 binary digits. 2^53 = 1,170,410,000,001,000 so you should have 15 to 16 decimal digits available.

Unfortunately, testing seems to indicate that there is a 14 decimal digit maximum. I'm not sure why that would be. In any case, 12 digits should be fine. On further testing, I would suggest to check with AB international (QlikTech) Team.

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)