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: 
Anonymous
Not applicable

Date Generation within time range

Hi all,

How to generate date within specific time range in master calendar.

If suppose time range is yesterday 5pm to today 5pm (03/04/2018 17:00:01 to 04/04/2018 17:00:00)

Can anyone help me to generate the date ??

Thanks in advance.Let me know for more details.

12 Replies
Anil_Babu_Samineni

How you want to generate? By every hour / minute / Second bases??

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
avkeep01
Partner - Specialist
Partner - Specialist

For hours:

LOAD

TIMESTAMP(TIMESTAMP('03/04/2018 17:00:01')+(RECNO()/24)) AS Datetime

AutoGenerate(FLOOR(TIMESTAMP('2018/04/04 17:00:00')*24)-FLOOR(TIMESTAMP('2018/04/03 17:00:01')*24));

avkeep01
Partner - Specialist
Partner - Specialist

Per minute:

LOAD

TIMESTAMP(TIMESTAMP('03-04-2018 17:00:00')+(RECNO()/(24*60))) AS Datetime

AutoGenerate(FLOOR(TIMESTAMP('2018-04-04 17:00:00')*24*60)-FLOOR(TIMESTAMP('2018-04-03 17:00:01')*24*60));

Per second:

LOAD

TIMESTAMP(TIMESTAMP('03-04-2018 17:00:00')+(RECNO()/(24*60*60))) AS Datetime

AutoGenerate(FLOOR(TIMESTAMP('2018-04-04 17:00:00')*24*60*60)-FLOOR(TIMESTAMP('2018-04-03 17:00:01')*24*60*60));

Anonymous
Not applicable
Author

Hi Anil,

please refer my attachment.

Date and Time field was already in database.

The "created" field which i have created by using below script.

If( WeekDay(Date)<>'Sun',

     if(hour(Time)>=17,date(Date+1,'DD/MM/YYYY'),date(Date,'DD/MM/YYYY')),

     if(hour(Time)>=13,date(Date+1,'DD/MM/YYYY'),date(Date,'DD/MM/YYYY'))

     )as Created,

The "created"   Field should be linked with master calendar.

But when i link with Master Link Date , it is showing null.

It is not generating the Date.

Date Issue.PNG

Anonymous
Not applicable
Author

Thanks for your reply.

But i want create for all dates .

Also for sunday , the time ended with 1 PM.

Remaining days 5PM to 5PM,

Please see my screen shot and previous replyDate Issue.PNG

avkeep01
Partner - Specialist
Partner - Specialist

Try

DAYSTART(If( WeekDay(Date)<>'Sun',

     if(hour(Time)>=17,date(Date+1,'DD/MM/YYYY'),date(Date,'DD/MM/YYYY')),

     if(hour(Time)>=13,date(Date+1,'DD/MM/YYYY'),date(Date,'DD/MM/YYYY'))

     )) as Created,

Anonymous
Not applicable
Author

Hi Van,

My requirement is, I want to auto Generate the date in master Calendar.

"

DAYSTART(If( WeekDay(Date)<>'Sun',

     if(hour(Time)>=17,date(Date+1,'DD/MM/YYYY'),date(Date,'DD/MM/YYYY')),

     if(hour(Time)>=13,date(Date+1,'DD/MM/YYYY'),date(Date,'DD/MM/YYYY'))

     )) as Created,

"

I have done this one in the table itself..this "Created"field--->Need to link with master Calendar .

avkeep01
Partner - Specialist
Partner - Specialist

What script did u use to generate the master calender? Is the master calender only based on the existing dates in the dataset?

U could generate a calender with this script for example:

FOR y = 2014 TO YEAR(TODAY())

LET vStartDate = DATE(DAYSTART(MAKEDATE($(y),1,1)),'YYYY-MM-DD');

LET vEndDate = DATE(DAYSTART(YEAREND(MAKEDATE($(y),1,1))),'YYYY-MM-DD');

temp01_Calendar:

LOAD

DATE('$(vStartDate)' + RECNO()-1,'YYYY-MM-DD') AS Date

AUTOGENERATE((vEndDate-vStartDate)+1);

NEXT y

Anonymous
Not applicable
Author

The below script i have used to generate ,

LET varMinDate = Num(MakeDate(2015,01,01));  

LET varMaxDate = Floor(MonthEnd(Today()));  

LET varToday = Date(Today(), 'MM/DD/YYYY'); 



LET varYesterday = Date(Today()-1, 'MM/DD/YYYY'); 

LET varCurrentMonth = Date(Monthstart(Today()-1), 'MMM-YYYY'); 

 

TempCalendar: 

   

LOAD 

$(varMinDate)+Iterno()-1 AS Num,

Date($(varMinDate)+Iterno()-1) AS TempDate 

AUTOGENERATE 1 WHILE $(varMinDate)+Iterno()-1<= num(today()); 



CustomCalendar:

LOAD  


Date(TempDate,'DD/MM/YYYY') as custom_LinkDate

resident TempCalendar;



So , I have to link the "Created" field with "custom_LinkDate".

It should be autogenerate the date within specific time range