Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. $300 savings extended to February 9th Learn More
Highlighted
madhubabu
New Contributor III

Date function

Hi all,

I have following table;

T1:

Load * Inline [

s.no   | Date                          | Products

1      |  08/06/2013 5:53:09      | soaps

2      |  09/10/2013 9:25:09      | pens

3      | 10/11/2014 10:55:25      | pencils

4      | 11/12/2015 11:25:30      | slates

];

I want to separate the Date field as date , month, year and remaining all will be the Time.

I have to get as following table.

s.no     |      Date      |      month      |      year      |      Time       | Products

1          |      08         |      06            |      2013     |      5:53:09   | soaps

2          |      09         |      10            |      2013     |      9:25:09   | pens

3          |      10         |      11            |      2014     |      10:55:25 | pencils

4          |      11         |      12            |      2015     | 11:25:30      | slates

Thanks,

Madhu

4 Replies
MVP
MVP

Re: Date function

PFA

SET TimestampFormat='DD/MM/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';

T1:

Load *,

  Day(trim(Date)) as Date2,

  Month(Date) as Month,

  Year(Date) as Year,

  Time(Date) as Time Inline [

s.no   | Date                          | Products

1      | 08/06/2013 5:53:09      | soaps

2      | 09/10/2013 9:25:09      | pens

3      | 10/11/2014 10:55:25      | pencils

4      | 11/12/2015 11:25:30      | slates

] (delimiter is '|');

miguelbraga
Valued Contributor III

Re: Date function

Do as follow:

T1:

LOAD *,

          Day(date(floor(Date))) as Date,

          Month(date(floor(Date))) as month,

          Year(date(floor(Date))) as year,

          time(Date) as time;

Load * Inline [

s.no   | Date                          | Products

1      |  08/06/2013 5:53:09      | soaps

2      |  09/10/2013 9:25:09      | pens

3      | 10/11/2014 10:55:25      | pencils

4      | 11/12/2015 11:25:30      | slates

];

This will get your desired output

Regards,

MB

miguelbraga
Valued Contributor III

Re: Date function

See the attached file

Regards,

MB

balrajahlawat
Esteemed Contributor

Re: Date function