topic Re: Timestamp conversion to Date in QlikView

Timestamp conversion to Date

Tue, 03 Sep 2013 14:37:30 GMT

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

Re: Timestamp conversion to Date

jpapador — Tue, 03 Sep 2013 14:39:02 GMT

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.

Re: Timestamp conversion to Date

Thu, 06 Feb 2014 19:49:10 GMT

This gives lexical element error

Re: Timestamp conversion to Date

Anonymous — Thu, 06 Feb 2014 19:54:03 GMT

Chuck

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

Could you paste in the load script ?

Best Regards, Bill

Re: Timestamp conversion to Date

Thu, 06 Feb 2014 20:07:32 GMT

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')

Re: Re: Timestamp conversion to Date

Anonymous — Thu, 06 Feb 2014 20:25:48 GMT

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

Re: Re: Timestamp conversion to Date

Thu, 06 Feb 2014 20:33:39 GMT

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 ....