Issues converting a Timestamp into Date

[#1]

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

[Gysbert_Wassenaar]

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

[#3]

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
;

[Gysbert_Wassenaar · Accepted solution]

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;

[#5]

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

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