Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
qvraj123
Contributor II

date format in .csv (excel) as general - 2015-09-02 08:00:20.178081-04:00

Hi All,

Thanks for your time,

I have this time stamp field in .csv file - I tried couple of the following but nothing works

DATE(FLOOR(NUM(activity))) AS ACTIVITY - did not work

DATE(Timestamp#( activity , 'YYYY-MM-DD h:mm:ss:tt'), 'MM/DD/YYYY' )  AS ACTIVITY1 - did not work

I have the same pattern in the entire file

2015-09-11 15:38:28.313892-04:00

2015-09-11 10:29:35.335317-04:00

2015-09-09 16:46:24.507198-04:00

2015-09-09 16:21:44.146465-04:00

2015-09-02 08:00:20.178081-04:00

2015-09-02 07:50:14.447959-04:00

2015-08-28 10:05:40.307276-04:00

2015-08-21 10:27:02.903171-04:00

2015-08-17 14:19:47.668255-04:00

2015-08-11 16:54:47.354771-04:00

2015-08-05 14:36:34.652504-04:00

2015-07-22 15:00:25.483051-04:00

Thanks again,

Rj

1 Solution

Accepted Solutions
harsh44_bhatia
Contributor

Re: date format in .csv (excel) as general - 2015-09-02 08:00:20.178081-04:00

try this

Temp:

Load

Date(date#(left(activity,10),'YYYY-MM-DD'),'MM/DD/YYYY') as Activity;

LOAD * INLINE [

activity

2015-08-17 14:19:47.668255-04:00

2015-08-11 16:54:47.354771-04:00

2015-08-05 14:36:34.652504-04:00

2015-07-22 15:00:25.483051-04:00

];

View solution in original post

9 Replies
harsh44_bhatia
Contributor

Re: date format in .csv (excel) as general - 2015-09-02 08:00:20.178081-04:00

Hi,

Can you state what result set you want to achieve, i.e. only Date or time stamp till micro sec

Re: date format in .csv (excel) as general - 2015-09-02 08:00:20.178081-04:00

Try this:

Date(Floor(TimeStamp#(Left(activity, 19), 'YYYY-MM-DD hh:mm:ss')), 'MM/DD/YYYY') as ACTIVITY1

harsh44_bhatia
Contributor

Re: date format in .csv (excel) as general - 2015-09-02 08:00:20.178081-04:00

try this

Temp:

Load

Date(date#(left(activity,10),'YYYY-MM-DD'),'MM/DD/YYYY') as Activity;

LOAD * INLINE [

activity

2015-08-17 14:19:47.668255-04:00

2015-08-11 16:54:47.354771-04:00

2015-08-05 14:36:34.652504-04:00

2015-07-22 15:00:25.483051-04:00

];

View solution in original post

Highlighted
Not applicable

Re: date format in .csv (excel) as general - 2015-09-02 08:00:20.178081-04:00

Look like the timezone attached to time stamp field.

Try like below:

Timestamp#(LEFT(TimeField ,19),'YYYY-MM-DD hh:mm:ss')

MVP
MVP

Re: date format in .csv (excel) as general - 2015-09-02 08:00:20.178081-04:00

Data:

Load

  Activity,

  TimeStamp(TimeStamp#(Activity,'YYYY-MM-DD hh:mm:ss.ffffff-ff:ff')) as ActivityTimeStamp,

  Date(Floor(TimeStamp#(Activity,'YYYY-MM-DD hh:mm:ss.ffffff-ff:ff'))) as ActivityDate,

  Time(Frac(TimeStamp#(Activity,'YYYY-MM-DD hh:mm:ss.ffffff-ff:ff'))) as ActivityTime

Inline

[

  Activity

  2015-09-11 15:38:28.313892-04:00

  2015-09-11 10:29:35.335317-04:00

  2015-09-09 16:46:24.507198-04:00

  2015-09-09 16:21:44.146465-04:00

  2015-09-02 08:00:20.178081-04:00

  2015-09-02 07:50:14.447959-04:00

  2015-08-28 10:05:40.307276-04:00

  2015-08-21 10:27:02.903171-04:00

  2015-08-17 14:19:47.668255-04:00

  2015-08-11 16:54:47.354771-04:00

  2015-08-05 14:36:34.652504-04:00

  2015-07-22 15:00:25.483051-04:00

];

MVP
MVP

Re: date format in .csv (excel) as general - 2015-09-02 08:00:20.178081-04:00

you can also use

TimeStamp(TimeStamp#(Activity,'YYYY-MM-DD hh:mm:ss.ffffff-04:00')) as ActivityTimeStamp,
Date(Floor(TimeStamp#(Activity,'YYYY-MM-DD hh:mm:ss.ffffff-04:00'))) as ActivityDate,
Time(Frac(TimeStamp#(Activity,'YYYY-MM-DD hh:mm:ss.ffffff-04:00'))) as ActivityTime
qvraj123
Contributor II

Re: date format in .csv (excel) as general - 2015-09-02 08:00:20.178081-04:00

Thank you All,

I just need the date part

DATE(FLOOR(DATE#(LEFT(activity,10), 'YYYY-MM-DD')),'MM/DD/YYYY' )  - this worked

Thank you Sunny, Manish,

  

qvraj123
Contributor II

Re: date format in .csv (excel) as general - 2015-09-02 08:00:20.178081-04:00

Hi Sunny

Could you please provide some suggestions on my thread with derive buckets

I got stuck - need some help

Rj

Re: date format in .csv (excel) as general - 2015-09-02 08:00:20.178081-04:00

You are getting advice from one of the experts on the community. I am not sure how much I can be of help here if he isn't able to help

But I will look at the issue and let you know if I can help