topic Re: Calendar : Date (MM/DD/YYYY) + Time(hh:mm:ss ampm) in QlikView

Calendar : Date (MM/DD/YYYY) + Time(hh:mm:ss ampm)

jonathan_dau — Thu, 22 Dec 2011 14:18:28 GMT

Hi all,

I know there are several posts on autogenerate calendar but I've hardly found some standard solution to autogenerate time.

I'm actually using the following script to generate date:

LET VAR_START_DATE = Num (MakeDate(2010,01,01));
LET VAR_END_DATE = Num (MakeDate(2015,12,31));

TMP_DATE:
LOAD Date($(VAR_START_DATE)+IterNo()-1,'MM/DD/YYYY') AS TmpDate
AUTOGENERATE 1 WHILE ($(VAR_START_DATE)+IterNo()-1) <= $(VAR_END_DATE);
DATE:
LOAD    
                               TmpDate AS Date_Cal,
                               Week(TmpDate) AS Week_Cal,
                               Year(TmpDate) AS Year_Cal,
                               Month(TmpDate) AS Month_Cal,
                               Day(TmpDate) AS Day_Cal,
                               WeekDay(TmpDate) AS WeekDay_Cal,
                               Date(MonthStart(TmpDate), 'YYYY-MMM') AS YearMonth_Cal,
                               'W' & WeekYear(TmpDate) & '-' & if (Week(TmpDate) < 10, '0'&Week(TmpDate), Week(TmpDate)) AS YearWeek_Cal
RESIDENT TMP_DATE 
ORDER BY TmpDate Asc;
drop table TMP_DATE;

Do you think it would be possible to integrate time generation using the same type of loop ? Would you have a solution that can generate time in a format 12h hh:mm:ss am/pm ? Thank you in advance.

Regards

Calendar : Date (MM/DD/YYYY) + Time(hh:mm:ss ampm)

swuehl — Thu, 22 Dec 2011 14:25:28 GMT

You can create date + time = timestamps in a similar way, of course.

In what resolution / step size do you want to create this time stamp? What do you want to achieve with this master timestamp calendar?

Calendar : Date (MM/DD/YYYY) + Time(hh:mm:ss ampm)

jonathan_dau — Thu, 22 Dec 2011 14:34:51 GMT

Hi Swuehl, thank you for your answer

This calendar will be used to manage batches and logs on server and to monitor the elapsed time between the beginning and the end of a job.

As a server is working all day long we need to get a timestamp from 00:00:00 to 23:59:59 but in a Anglo-Saxon format so 12h hh:mm:ss AM/PM.

I'm struggling to create this timestamp, if you have a solution please let me know.

Thank you again

Regards

Calendar : Date (MM/DD/YYYY) + Time(hh:mm:ss ampm)

Miguel_Angel_Baeyens — Thu, 22 Dec 2011 15:33:45 GMT

Hi,

Check this post on how to create a calendar to store all values from dates and times within each date.

Hope that helps.

Miguel

Re: Calendar : Date (MM/DD/YYYY) + Time(hh:mm:ss ampm)

Thu, 22 Dec 2011 15:39:50 GMT

You could also try that script:

LET VAR_START_DATE = Num (MakeDate(2010,01,01));
LET VAR_END_DATE = Num (MakeDate(2015,12,31));
LET vInterval=86400; // 86400=seconds
LET vIterations= (VAR_END_DATE - VAR_START_DATE + 1) * vInterval;

DATE:
LOAD
Date(TmpDateTime) AS Date_Cal,

TmpDateTime AS DateTime_Cal,
                               Week(TmpDateTime) AS Week_Cal,
                               Year(TmpDateTime) AS Year_Cal,
                               Month(TmpDateTime) AS Month_Cal,
                               Day(TmpDateTime) AS Day_Cal,
                               WeekDay(TmpDateTime) AS WeekDay_Cal,
                               Date(MonthStart(TmpDateTime), 'YYYY-MMM') AS YearMonth_Cal,
                               'W' & WeekYear(TmpDateTime) & '-' & if (Week(TmpDateTime) < 10, '0'&Week(TmpDateTime), Week(TmpDate)) AS YearWeek_Cal
;
LOAD Timestamp($(VAR_START_DATE)+ ((IterNo()-1)/$(vInterval)),'MM/DD/YYYY h:mm:ss tt') AS TmpDateTime
AUTOGENERATE 1 WHILE (IterNo() <= $(vIterations));

Best regards

Michael

Re: Calendar : Date (MM/DD/YYYY) + Time(hh:mm:ss ampm)

johnw — Thu, 22 Dec 2011 21:45:14 GMT

I remember having serious problems trying to get the autogenerated hours to match other versions of hours - I had little tiny value differences in like the millionths place, that sort of thing. Anyway, that's what led me to the rather complicated expression for "Hour" in the script below, used in one of my applications. Hopefully it's clear where to adjust the format to match what you want. (Not that you'd copy this script exactly, as turn definitions and the calendar QVD are specific to our company.)

[Calendar]:

LOAD

"Hour"

,"Turn"

,"Date"

,dual("Date" & ' ' & "Turn","Turn Start") as "Date Turn"

;

LOAD *

,date(daystart("Turn Start")-(hour("Turn Start")=21)) as "Date"

,pick(match(hour("Turn Start"),21,5,13),'A','B','C') as "Turn"

;

LOAD

timestamp(timestamp#(text(timestamp($(vFirstTurn)+(recno()-1)/24, 'YYYYMMDDhh')),'YYYYMMDDhh'),'MM/DD/YY hh:mm') as "Hour"

,timestamp(timestamp#(text(timestamp($(vFirstTurn)+floor((recno()-1)/8)/3,'MM/DD/YY hh:mm:ss')),'MM/DD/YY hh:mm:ss')) as "Turn Start"

AUTOGENERATE floor(24*(now(0)-$(vFirstTurn)))+2

;

LEFT JOIN ([Calendar])

LOAD

"Date"

,"Week Ending" as "Week"

,"Month"

,"Year"

,"Payroll Week Number"

FROM Calendar.qvd (QVD)

;

Re: Calendar : Date (MM/DD/YYYY) + Time(hh:mm:ss ampm)

jonathan_dau — Fri, 23 Dec 2011 07:42:48 GMT

Thank you Miguel, Michael and John. With your input I did implement a master timestamp calendar though I still have some problems to match the timestamp format. I think it is also linked to my OS and my French configuration ; ).
Thank you anyway

Regards