Timestamp conversion to Date

[#1]

Hi,

I have read other discussions on this subject, but none of the solutions seem to work for me.

I'm trying to convert data from a .xlsx table in the format 13/04/2013 14:35:00 +00:00 into a date.

My script set statements currently include:

SET DateFormat='D/M/YYYY';

SET TimestampFormat='D/M/YYYY hh:mm:ss[.fff]';

Can you advise how i can get the data above (named [Transaction Date]) to be displayed as 13/4/2013 (also named as [Transaction Date])?

I'm new to qlikview & would really appreciate some help.

Many Thanks

[jpapador]

Date(Floor([Transaction Date]), 'DD/M/YYYY') as [Transaction Date]

You would do this in the load script as you are bringing in the field.

[#3]

This gives lexical element error

[Anonymous]

Chuck

That sounds like Microsoft ODBC error, not a QlikView one.

Could you paste in the load script ?

Best Regards, Bill

[#5]

The whole script is below....minus the ODBC connect string. I beleive I have tried every single thing that I could figure out on this site as a suggestion. What I need is something like this

ODBC

SQL

Select

PBI."Submit Date", // --this is a timestamp field / column in SQL

Month(PBI."Submit Date") as Month,

Year(PBI."Submit Date) as Year,

FROM "PBM:Problem Investigation" PBI

____________________________Original Below________________________________________

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

load dual(String,Num) as Month inline

[String,Num

Jan,1

Feb,2

Mar,3

Apr,4

May,5

June,6

July,7

Aug,8

Sept,9

Oct,10

Nov,11

Dec,12

];

PBI:

SQL   SELECT

  PBI.Assignee,

    PBI."Assigned Group",

    PBI."Assignee Pblm Mgr",

//    PBI."Category",

  PBI."Assigned Group Pblm Mgr" as "PM Group",

    PBI."Description" as "Short Description",

    PBI."Detailed Decription" as "Description",

    PBI."First Name",

    PBI."First Reported On",

//    MONTH(PBI."First Reported On") as "Month",

    PBI.Impact,

    PBI."Invesitgation Status Reason",

    PBI."Investigation Driver",

    PBI."Investigation Status" as Status,

//    PBI."Known Error Created",

    PBI."Known Error Created Date",

    PBI."Last Modified By",

    PBI."Last Modified Date",

//    month(Timestamp#(PBI.'Last Modified Date', 'MM/DD/YYYY hh:mm:ss')) as Month,

    PBI."Last Name",

//    PBI."Organization",

  PBI."PBM_CI" as "Problem CI",

    PBI."Prev Status-PBI" as "Previous Status",

    PBI.Priority,

    PBI."Problem Investigation ID" as "Ticket Number",

    PBI."Product Name",

//    PBI.ServiceCI,

//    PBI."Status History",

    PBI."Status History.Assigned.TIME" as "Assigned Time",

//    PBI."Status History.Cancelled.TIME",

//    PBI."Status History.Cancelled.USER",

//    PBI."Status History.Closed.TIME",

//    PBI."Status History.Closed.USER",

    PBI."Status History.Completed.TIME" as "Completed Time",

    PBI."Status History.Completed.USER" as "Completed User",

    PBI."Status History.Under Investigation.TIME" as "Investigation Time",

    PBI."Status History.Under Investigation.USER" as "Investigation User",

    PBI."Status History.Under Review.TIME" as "Under Review Time",

    PBI."Status History.Under Review.USER" as "Under Review User",

    PBI."Submit Date",

    PBI."Submitter",

    PBI."Support Group Name Requester",

    PBI."Target Resolution Date",

    PBI."Temporary Workaround" as "Workaround",

    PBI.Urgency,

FROM "PBM:Problem Investigation" PBI

WHERE

PBI."Investigation Status" not in ('Cancelled','Closed')

and "Assigned Group Pblm Mgr" in ('Problem Managers','USB PM Pblm Mgr - DEFAULT')

[Anonymous]

Chuck

Maybe what you need is a preceding load.  The below is a very stripped down version derived from your script, does it work for you ?

PBI:

load

  Date(Floor([Last Modified Date]), 'DD/M/YYYY') as [Last Modified Date]

;

SQL  SELECT

  PBI."Last Modified Date" as "Last Modified Date"

FROM "PBM:Problem Investigation" PBI

WHERE

PBI."Investigation Status" not in ('Cancelled','Closed')

and "Assigned Group Pblm Mgr" in ('Problem Managers','USB PM Pblm Mgr - DEFAULT')

;

The SQL SELECT retrieves the data and then passes it up to the load preceding it, which does the QlikView manipulation to return the pure date.

Best Regards,     Bill

[#7]

Thanks Bill but I dont have a field PBI."Last Modified Date"

I tried the way you said and the way below and both errored out

Should it be like this

SQL  SELECT

  PBI."Submit Date" as "Last Modified Date",

Where ....