topic Issues converting a Timestamp into Date in QlikView

Issues converting a Timestamp into Date

Wed, 24 Apr 2013 15:27:47 GMT

Hi all,

I am loading from Salesforce and I´d like to convert a timestamp variable (dd.mm.yyyy hh:mm.ss) into a Dateformat (dd.mm.yyyy). I´ve tried several ways but nothing groups by day (simply cuts the end of the timestamp).

Thanks,
David

Re: Issues converting a Timestamp into Date

Gysbert_Wassenaar — Wed, 24 Apr 2013 16:40:17 GMT

You may have to use the floor function to completely remove the time part of the timestamp: floor(MyTimestamp) as MyDate.

Re: Issues converting a Timestamp into Date

Thu, 25 Apr 2013 12:37:25 GMT

Thanks Gysbert for answering!

I´ve tried as you suggested but unfortunately it doesn´t work. I get the following error message:

"floor(CreatedDate) as MyDate, ERROR at Row:23:Column:19 unexpected token: as".

Could you check what I´am doing wrong?

Thanks, David

Here is the script:

//SALESFORCE DATA

CUSTOM CONNECT TO "Provider=SalesForceDLL.dll;F27;XUserId=removed;XPassword=removed;";

Load

CreatedDate,
floor(CreatedDate) as MyDate

;

SELECT

CreatedDate,
floor(CreatedDate) as MyDate,

from case
;

Re: Issues converting a Timestamp into Date

Gysbert_Wassenaar — Thu, 25 Apr 2013 12:48:18 GMT

To begin with Salesforce doesn't seem to understand the AS keyword. That's what it's complaining about. Very likely the Salesforce database also won't understand Qlikview functions like floor.

Something to keep in mind is that Qlikview sends sql statements (Select ... from ... etc) directly to the database. Qlikview itself doesn't parse the sql statement. The sql statement should be in the dialect the database understands.

So, use Qlikview functions in the preceding load:

CUSTOM CONNECT TO"Provider=SalesForceDLL.dll;F27;XUserId=removed;XPassword=removed;";

Table1:
LOAD CreatedDate, floor(CreatedDate) as MyDate;
SELECT CreatedDate from case;

Re: Issues converting a Timestamp into Date

Thu, 25 Apr 2013 13:21:42 GMT

Hi Gysbert, it works - thanks a lot !! 🙂

ps. could you also take a look into this one: http://community.qlik.com/message/339436#339436