topic Time Stamp Analysis in QlikView

Time Stamp Analysis

Mon, 27 Feb 2012 20:58:47 GMT

Hi,

Anybody knows how to read the timeStamp.

I have the attached document. It loads phone log files from a directory.

I am trying to have the date, time shown on a table next to the Original Party and Destination party.

I tried the Time Stamp option on a list box just for testing..It doesn't seem to be correct..

Any Advice?

Thxs,

Badr

Time Stamp Analysis

Mon, 27 Feb 2012 21:03:12 GMT

BTW, the date should be today's date and the time is between 12 and 2.00 PM

Re: Time Stamp Analysis

swuehl — Mon, 27 Feb 2012 21:35:43 GMT

Hm, that's pretty hard since you don't give any information about the source system you get your data from. The source data will define how the integer number called TimeStamp needs to be interpreted.

I just make a guess that you are using a format that is compatible to a Cisco cdr (number of seconds since Jan 1st 1970).

Try this as additional expression in your TimeStamp listbox (expression tab in list box properties):

=timestamp(TimeStamp/86400+makedate(1970,1,1))

This gives slightly different timestamps than you posted in your second post, but close.

But maybe my assumptions are wrong, so please double check the source format definition.

If the transformation is ok, you could the transformation in the script like

LOAD

...

timestamp(TimeStamp/86400+makedate(1970,1,1)) as TimeStamp

...

from ...;

Another idea is to get rid of the format strings that appear on some lines (like 'INTEGER'), maybe by using a where clause :

LOAD

...

from Table.xls where isnumber(TimeStamp);

Hope this helps,

Stefan

Time Stamp Analysis

Mon, 27 Feb 2012 21:52:29 GMT

This is great but the time that showing there is wrong like it shows the time +5 hours, is there anyway to fix it?

I am loading from Cisco log files.

the second thing is the were statement is not working but it will be great if I can get rid of them.

Thank you so much!

Re: Time Stamp Analysis

swuehl — Mon, 27 Feb 2012 22:01:34 GMT

Is -5 hours maybe your TimeZone offset?

You could probably correct the timestamp like this

=timestamp(TimeStamp/86400+makedate(1970,1,1)+interval#(-5,'h'))

(please check if the sign of the first argument to interval# function is the way you need it).

Regarding your other question, I misspelled the function name, try

LOAD

...

from Table.xls where isnum(TimeStamp);

Time Stamp Analysis

Mon, 27 Feb 2012 22:06:19 GMT

Thank you so much,

You helped me so many times..

How did you get all this experience..Great Job!

Re: Time Stamp Analysis

swuehl — Tue, 28 Feb 2012 18:49:01 GMT

You're welcome.

One more thought. If it's really a time zone issue that we need to correct for the -5 hours, it's probably better to use converttolocaltime function, that will also consider DST.

=converttolocaltime(timestamp(TimeStamp/86400+makedate(1970,1,1)),'Indiana (Ost)')

Instead of 'Indiana (Ost)', use your place string as defined in your OS for your time zone, or you could also use 'UTC-05:00'

Regards,

Stefan