topic Removing the Time from Timestamp from Google Doc in QlikView

Removing the Time from Timestamp from Google Doc

Fri, 27 May 2011 17:59:30 GMT

I'm trying to remove the time from the Timestamp of a google doc spreadsheet. I need to get the date only for display and grouping purposes.

Anyone have an idea?

Dan

Removing the Time from Timestamp from Google Doc

Sat, 28 May 2011 12:58:29 GMT

Hi Dan,

To convert a timestamp to a date, you need to round the underlying numeric value to remove the decimals corresponding to the time. After this you convert the numeric value to a date.

Example:
LOAD
date(floor(TimestampField)) AS Date
FROM DataSource;

Kind regards

Staffan

Re: Removing the Time from Timestamp from Google Doc

Tue, 31 May 2011 15:21:28 GMT

When I add the code from above it loads a blank field. I've added a quick Qlikview that has a link to a timestamp in a Google Doc.

Dan

Re: Removing the Time from Timestamp from Google Doc

Mon, 06 Jun 2011 19:34:30 GMT

Does anyone have any ideas on getting the timestamp out of a google doc?

I tried the date(floor(timestampfield)) as DATE but it did not work for me. Can anyone make it work?

Dan

Re: Removing the Time from Timestamp from Google Doc

nagaiank — Mon, 06 Jun 2011 20:32:50 GMT

Try

Subfield(Google_Date,' ',1) as DatePart

Removing the Time from Timestamp from Google Doc

Tue, 07 Jun 2011 02:17:11 GMT

Are you trying to get the time or the date? date(floor(timestampfield)) will give you the date associated with the field and would give you a null if you tried to specify it was a timestamp.

If you have something like

12//01/2011 10:55:22

and you wanted to get 10:55:22 you could do the following:

timestamp(right(timestampfield,8))

timestamp(subfield(timestampfield,' ',2)).

Re: Removing the Time from Timestamp from Google Doc

rwunderlich — Tue, 07 Jun 2011 05:58:15 GMT

The datetime field is a string so you need to use the timestamp#() function to read it. eg

timestamp#(F2,'MM/DD/YYYY hh:mm:ss') as Datetime_Google

Attached is a an example -- using preceeding load instead of the RESDENT load. The timestamp#() method remains the same.

-Rob

Re: Removing the Time from Timestamp from Google Doc

colinh — Tue, 07 Jun 2011 07:21:28 GMT

You can also use dayname() instead of date(floor()). dayname() sets the timestamp to 12:00:00.

dayname(Datetime_Google) as Date should do the same as date(floor(Datetime_Google)) as Date.

Re: Removing the Time from Timestamp from Google Doc

Tue, 07 Jun 2011 07:38:55 GMT

Rob's addition definitely should get this to work for you.

Just wanted to add a note regarding the use of string functions as left() and right() to convert timestamps.
What you need to keep in mind is that timestamps, dates and times are represented by both a string value and a numeric value.

Example:
String | Numeric
2011-06-07 09:00:00 | 40701.375

2011-06-07 12:00:00 | 40701.5

If you were to read this field using left(Field, 10), you would strip the time from the string representation but not from the numeric value. The effect this has is that they will be considered as two different values by QlikView and will not link to the same values in other fields.

To avoid this you should instead modify the numeric value and then convert the field using e.g. date() to the desired string representation.

Re: Removing the Time from Timestamp from Google Doc

Tue, 07 Jun 2011 13:38:31 GMT

swn,

Thanks for the explaining. One question though. If you use the TimeStamp#() function don't you keep the numerical date but only format the field to a timestamp? If Sith88 is trying to get only get the timestamp, regardless of day, is there any way do this without converting it to a string?

In the past I've had to resort to timestamp#(timestamp(right(F2,8))) to get it to match other true timestamp fields.

Re: Removing the Time from Timestamp from Google Doc

Tue, 07 Jun 2011 14:33:18 GMT

Thanks to all for the help. Krishnamoorthy solution worked well. I'm not sure of how Google keeps timestamps but it appears to be different from the way other systems keep timestamps. Dan

Re: Removing the Time from Timestamp from Google Doc

Wed, 08 Jun 2011 08:45:01 GMT

I think that we are talking about a few different things here actually. As I interpreted the original question, sith88 is looking to read only the date part of the timestamp and not the time. Reading only the time will require a different approach. However, what you in all cases need to keep in mind is that dates, times and timestamps (date + time) include both a string representation and a numeric underlying value and that this needs to be taken in account when converting these values.

A note regarding timestamp#(field[, format]), this function doesn't format the values, but actually only tells QlikView that the field is a timestamp. This applies to date#() and time#() as well.
These functions are particularly useful when reading values which are not formatted according to your standard system formatting.
You can nest them together with their formatting equivalent to convert them to your standard format instead. Example:
date(date#('2011_06_08', 'YYYY_MM_DD'))
If your system format is DD/MM/YYYY, this would return 08/06/2011.

Removing the Time from Timestamp from Google Doc

Wed, 18 Apr 2012 06:57:46 GMT

Hi All,

Could you please provide steps or any doucument for fetching data from Google doc. Becuase I tried but could not success . Thanks lot in advance.

Arunesh