Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Can anyone please help me in writing expression to segregate the date into day, month and year from the Date field.
In the script, you can do this:
LOAD Day(Date) as Day,
Month(Date) as Month,
Year(Date) as Year,
Date
OtherFields
FROM Source;
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.
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.
year( '1971-10-30' ) returns 1971.
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
If you have a date field
You can simply do
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,
the format code change as per your string format.
HTH
Sasi
For any Dates you can segregate into following:
Day(Date) as Day,
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,
Kavita
