Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jonathan_dau
Contributor III
Contributor III

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

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

6 Replies
swuehl
MVP
MVP

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? 

jonathan_dau
Contributor III
Contributor III
Author

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

Miguel_Angel_Baeyens

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

Not applicable

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

johnw
Champion III
Champion III

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)

;

jonathan_dau
Contributor III
Contributor III
Author

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