topic Re: How to segregate the date. in QlikView

How to segregate the date.

Thu, 06 Aug 2015 06:39:21 GMT

Hi,

Can anyone please help me in writing expression to segregate the date into day, month and year from the Date field.

Re: How to segregate the date.

sunny_talwar — Thu, 06 Aug 2015 11:24:56 GMT

In the script, you can do this:

LOAD Day(Date) as Day,

Month(Date) as Month,

Year(Date) as Year,

Date

OtherFields

FROM Source;

Re: How to segregate the date.

maxgro — Thu, 06 Aug 2015 11:29:29 GMT

from QlikView help, date ant time functions, for script and charts

day(date)

Day. Returns an integer representing the day when the fraction of expr is interpreted as a date according to the standard number interpretation.

Example:

day( '1971-10-30' ) returns 30.

month(date)

Month. Returns a text string representing the month when the fraction of expr is interpreted as a date, but can be formatted as a number.

Example:

month( '1971-10-30' ) returns Oct.

year(date)

Year. Returns an integer representing the year when the fraction of expr is interpreted as a date according to the standard number interpretation.

Example:

year( '1971-10-30' ) returns 1971.

Re: How to segregate the date.

Thu, 06 Aug 2015 12:12:46 GMT

Qlikview has many date and time functions, they are very useful and simple to use

You can use these functions in scripting as well as in sheet objects

you can derive the new fields from already exiting date field like below

if you have the OrderDate as your field, you can derive Day, Month, Year and etc in simple way in scripting

Ex:

Load OrderDate,

Day(OrderDate) as Day, // Gives the day of the OrderDate like 2,3,....

Month(OrderDate) as Month, // Gives the Month of the OrderDate like jan, feb,.....

Year(OrderDate) as Year // Gives the Year of the OrderDate like 2012,2013,...

From Order; //Source Table

Regards,

Mahesh

Re: How to segregate the date.

sasiparupudi1 — Thu, 06 Aug 2015 12:19:59 GMT

If you have a date field

You can simply do

LOAD Day(Date) as Day,

Month(Date) as Month,

Year(Date) as Year,

resident table;

if you have date in the string format ex :YYYY-MM-DD,

then

LOAD Day(Date#(Date,'YYYY-MM-DD')) as Day,

Month(Date#(Date,'YYYY-MM-DD')) as Month,

Year(Date#(Date,'YYYY-MM-DD')) as Year,

resident table;

the format code change as per your string format.

HTH

Sasi

Re: How to segregate the date.

kavita25 — Thu, 06 Aug 2015 12:28:08 GMT

Hi,

For any Dates you can segregate into following:

Day(Date) as Day,

Month(Date) as Month,

Year(Date) as Year,

if you have date in the string format e.g. 25/May/2015

Day(Date#('25/May/2015','DD/MMM/YYYY')) as Day

Month(Date#('25/May/2015','DD/MMM/YYYY')) as Month,

Year(Date#('25/May/2015','DD/MMM/YYYY')) as Year,

Regards,

Kavita