topic Re: Master calendar with Timestamp in QlikView

Master calendar with Timestamp

Wed, 25 Nov 2020 16:16:04 GMT

can someone upload an example script for a master calendar with Date and Time PLEASE

I am trying to use the following script:

But I am getting an error

Load ID,

SaleDate,

floor(SaleDate) as SaleDateKey

;

LOAD * INLINE [

ID, SaleDate

1,2017-07-16 20:00:00

2,2017-07-16 21:00:00

3,2017-07-17 12:00:00

4,2017-07-17 13:00:00

5,2017-07-17 15:00:00

];

MinMaxDates:

LOAD Floor(Min(TimeStamp#(SaleDate, 'DD/MM/YYYY hh:mm:ss'))) as MinDate,

Floor(Max(TimeStamp#(SaleDate, 'DD/MM/YYYY hh:mm:ss'))) as MaxDate

RESIDENT a;

LET vMinDate = FieldValue('MinDate', 1);

LET vMaxDate = FieldValue('MaxDate', 1);

DROP TABLE MinMaxDates;

MasterCalendar1:

LOAD *,

Hour(AddedTimeStamp) as Hour,

Day(AddedTimeStamp) as Day,

Month(AddedTimeStamp) as Month;

LOAD

DayStart(TimeStamp($(vMinDate) + (RecNo()/60/24) + (IterNo() -1))) as AddedDate,

TimeStamp($(vMinDate) + (RecNo()/60/24) + (IterNo() -1)) as AddedTimeStamp

AUTOGENERATE 1439

WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate));

Re: Master calendar with Timestamp

sunny_talwar — Wed, 19 Jul 2017 11:06:44 GMT

Check this out

Master Calendar with Hours, Minutes & Seconds

But I suggest an alternative approach of creating a separate

The Master Time Table

Re: Master calendar with Timestamp

Wed, 19 Jul 2017 11:36:05 GMT

thanks, Sunney,

In order to understand the post :'The Master Time Table'

Do I need to create two MasterCalendar tables? can you upload an example

my goal is to create an input box in the UI so the user can select a range of date-time

(for example from 2017-07-16 20:00:00 to 2017-07-17 13:00:00).

how can I create the input box with start date &time and end date &time

Re: Master calendar with Timestamp

sunny_talwar — Wed, 19 Jul 2017 11:44:59 GMT

In order to understand the post :'The Master Time Table'

Do I need to create two MasterCalendar tables? can you upload an example

Yes, Master Calendar and Master Time. Unfortunately, I couldn't find a thread where you can see this, but you should be able to do it if you go through the thread by Henric. The script would look similar to master calendar script, but will just use the time portion of the timestamp field.

my goal is to create an input box in the UI so the user can select a range of date-time

(for example from 2017-07-16 20:00:00 to 2017-07-17 13:00:00).

Not sure why you need a date and time calendar for this? This could work without the calendar also

Re: Master calendar with Timestamp

Wed, 19 Jul 2017 11:59:21 GMT

so can you please help me to build an 'Input Box' with range of date-time

Attached an example qvw

*my original qvw file contains millions of records

I know how to build an input box only with date rang, but I want to customize it so that it holds time too.

Re: Master calendar with Timestamp

sunny_talwar — Wed, 19 Jul 2017 12:17:07 GMT

Check the attached

Re: Master calendar with Timestamp

Wed, 19 Jul 2017 13:07:26 GMT

Exactly what I wanted

Thanks!

Re: Master calendar with Timestamp

sunny_talwar — Wed, 19 Jul 2017 13:08:59 GMT

Awesome, please close the thread by marking correct and helpful responses.

Best,

Sunny

Re: Master calendar with Timestamp

Wed, 19 Jul 2017 14:32:49 GMT

sunny,

I ran into another question in my qvw.

I create a table box that counts the ID depending on the user's date selection.

count(DISTINCT {<SaleDate_DateFormat={">=$(v_MinDate) <=$(v_MaxDate)"}>}ID)

if the user filter the 'Year'/ 'Month','Day' list box the calculation is correct

but if he filter the time range I need to change the formula

count(DISTINCT {<SaleDate={">=$(VstartDate) <=$(VEndDate)"}>}ID)

is it possible to create only one formula that will calculate the user selection, no matter which date filter he select

Re: Master calendar with Timestamp

sunny_talwar — Wed, 19 Jul 2017 14:39:46 GMT

I am not sure I follow... are you talking about text box object? This should not be impacted by the range you enter?

Re: Master calendar with Timestamp

Wed, 19 Jul 2017 18:07:28 GMT

yes,tex object.

I have 2 types of calendars:

Calander I - in witch the user can select year, month, day. (vmindate & vmaxdate

Calander II - in witch the user can select date as well as time (vstartdate & venddate)

I want to create a text object that will calculate :

1. If the user select dates in calander I - count(DISTINCT {<SaleDate_DateFormat={">=$(vMinDate) <=$(vMaxDate)"}>}ID)

2. if the user selects date & time rage in calander 2 - count(DISTINCT {<SaleDate={">=$(VstartDate) <=$(VEndDate)"}>}ID)

Thanks

Re: Master calendar with Timestamp

sunny_talwar — Wed, 19 Jul 2017 19:03:12 GMT

I am only seeing a single master calendar in your app... I guess based on the selections you have in there what is your expected output?

Re: Master calendar with Timestamp

Wed, 19 Jul 2017 19:11:52 GMT

what I mean is that I have 2 calendars in the user interface....

the outcome should be the count of ID`s regardless of which calendar the user is using (but according to the user`s selection on the calendar used)

I hope its clear, if not - let me know.

thanks

Re: Master calendar with Timestamp

sunny_talwar — Wed, 19 Jul 2017 19:17:30 GMT

I guess it will help me if you can tell me the numerical output (like count will be 10) for the given selections and may be another set of outputs based on other selections.

Re: Master calendar with Timestamp

Wed, 19 Jul 2017 19:44:29 GMT

Ok,so I changed the variable Vmindate (attached new qvw)

if the user selects in calendar 1 the values:

year=2017

month=jul

day=17

the expected result= 5

if the user selects in calendar 2 ,the following time range

2017-07-17 00:00:00

2017-07-17 15:00:00

the expactedresult=3

Re: Master calendar with Timestamp

piotr-bratek — Mon, 21 Jun 2021 22:15:53 GMT

Hi Sunny,

is the Master Calendar with Hours, Minutes & Seconds article somehow restricted? I get a message "You do not have sufficient privileges for this resource or its parent to perform this action."

cheers
Piotr