topic Master calendar in QlikView

Master calendar

Anonymous — Mon, 20 Feb 2012 21:39:31 GMT

Hello,

I have some data with the date/time column, but the format is 12/02/2012 1:15AM... If I want to determine which day of the week is, is it possible with a master calendar or something like that?

Thanks!!!

Re: Master calendar

swuehl — Mon, 20 Feb 2012 21:46:36 GMT

Just take care that the format is correctly read in in your load:

SET TimestampFormat='DD/MM/YYYY h:mmTT';

LOAD

...

DateTimeColumn,

daystart(DateTimeColumn) as Date,

Time#(Time(DateTimeColumn) as Time,

weekday(DateTimeColumn) as Weekday,

...

from Table;

weekday() will get you the day of the week for each timestamp. Check out also the other date / time functions.

Regards,

Stefan

edit: removed line

Master calendar

Anonymous — Tue, 21 Feb 2012 15:19:03 GMT

Ok, and if I have some historic tables, the function weekday would take the exact day of the year?

Master calendar

swuehl — Tue, 21 Feb 2012 15:35:56 GMT

What do you mean with the exact day of the year? Maybe according

DayNumberOfYear(date[,firstmonth])

Returns the day number of the year according to a timestamp with the first millisecond of the first day of the year containing date. The function always uses years based on 366 days.

By specifying a firstmonth between 1 and 12 (1 if omitted), the beginning of the year may be moved forward to the first day of any month. If you e.g. want to work with a fiscal year starting March 1, specify firstmonth = 3.

Examples:

DayNumberOfYear(date) returns the day number counted from the first of the year.

DayNumberOfYear(date,3) returns the number of the day as counted from the first of March.

function?

Master calendar

Anonymous — Tue, 21 Feb 2012 15:41:55 GMT

If I want to format the weekday()... how can I do that... I'm getting "jan" and I want "january". The same issue with Month().

Thanks

Master calendar

swuehl — Tue, 21 Feb 2012 16:34:08 GMT

Not sure if I am missing an easier solution but you can use this

LOAD

DateField,

dual(date(DateField,'MMMM'),num(month(DateField))) as LongMonths,

dual(date(DateField,'WWWW'),num(weekday(DateField))) as LongWeekDays,

...

from Table;

to substitute the weekday() resp. month() function. Using the format codes 'MMMM' resp. 'WWWW' will return the long names set in the standard format settings or retrieved from the OS.

Regards,

Stefan

Master calendar

Tue, 21 Feb 2012 18:23:11 GMT

In your load script

SET MonthNames = 'january;february....';

SET DayNames = 'monday; tuesday.... ';