Master calendar with Timestamp

[#1]

Hi

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

a:

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)); 

[sunny_talwar]

Check this out

Master Calendar with Hours, Minutes &amp; Seconds

But I suggest an alternative approach of creating a separate

The Master Time Table

[#3]

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

[sunny_talwar]

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

[#5]

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.

[sunny_talwar · Accepted solution]

Check the attached

[#7]

Exactly what I wanted

Thanks!

[sunny_talwar]

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

Best,

Sunny

[#9]

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 

[sunny_talwar]

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