Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
IS this, you are expecting?
Timestamp(25569 + Round(Evaluate(Timestamp)/1000000)/86400)
Try this
=Timestamp(25569 + Round(WRITETIME/1000) / 86400)
Or this
=Timestamp(25569 + Round(WRITETIME/1000000) / 86400)
It returns as -- 02-02-2007 09:53:20
No this didn't work. On both examples I get a - .
IS this, you are expecting?
Timestamp(25569 + Round(Evaluate(Timestamp)/1000000)/86400)
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
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
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
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
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.