topic Re: Date function in QlikView

Date function

madhubabu — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

Re: Date function

tresB — Tue, 06 Sep 2016 09:55:18 GMT

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

Re: Date function

miguelbraga — Tue, 06 Sep 2016 09:56:07 GMT

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,

Re: Date function

miguelbraga — Tue, 06 Sep 2016 10:38:30 GMT

See the attached file

Regards,

Re: Date function

Anonymous — Tue, 06 Sep 2016 10:44:17 GMT

Why not check this?

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/DateAndTimeFunctions/date-time-functions.h…