topic Re: 16 digit # (exp:1170410000000000) from DB2 table - convert to time in QlikView

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

mp802377 — Tue, 20 Jun 2017 16:00:34 GMT

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 one	value 2	value3	value 4	value 5	value 6	value7	size	Timestamp	TMZDIFF	WRITETIME
0	0	0	0	0	0	0	0	1170410000001000	0	1170410000000000
0	0	0	0	0	0	0	0	1170410000001000	0	1170410000000000
0	0	0	0	0	0	0	0	1170410000001000	0	1170410000000000
7.97	7.97	8.11	7.97	8.11	56400	8.11	1	1170410000001000	0	1170410000000000
39.99	39.99	0	39.99	0	307	0	1	1170410000001000	0	1170410000000000
180.76	180.76	178.32	180.76	178.32	11902	178.32	5	1170410000001000	0	1170410000000000
203.26	203.26	0	203.26	0	32	0	7	1170410000001000	0	1170410000000000
335.31	335.31	0	335.31	0	135	0	7	1170410000001000	0	1170410000000000
599.24	599.24	592.26	599.24	592.26	5271	592.26	5	1170410000001000	0	1170410000000000

Thank you,

Martha

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

Anil_Babu_Samineni — Tue, 20 Jun 2017 16:06:33 GMT

Try this

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

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

Anil_Babu_Samineni — Tue, 20 Jun 2017 16:08:06 GMT

Or this

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

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

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

mp802377 — Tue, 20 Jun 2017 17:16:53 GMT

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

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

Anil_Babu_Samineni — Wed, 21 Jun 2017 01:52:27 GMT

IS this, you are expecting?

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

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

mp802377 — Wed, 21 Jun 2017 17:31:52 GMT

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

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

mp802377 — Wed, 21 Jun 2017 19:08:14 GMT

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

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

Anil_Babu_Samineni — Thu, 22 Jun 2017 02:22:34 GMT

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

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

mp802377 — Thu, 22 Jun 2017 15:54:03 GMT

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

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

Anil_Babu_Samineni — Thu, 22 Jun 2017 16:10:14 GMT

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.