Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

mp802377
Contributor

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

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

IS this, you are expecting?

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


Capture.PNG

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)
9 Replies

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

Try this

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

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)

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

Or this

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


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

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
Contributor

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

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

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

IS this, you are expecting?

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


Capture.PNG

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
Contributor

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

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
Contributor

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

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

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

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
Contributor

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

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

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.

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)