Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
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

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