Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sindhu_ibizcs
Contributor

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

Re: Date Generation within time range

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

avkeep01
Valued Contributor

Re: Date Generation within time range

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
Valued Contributor

Re: Date Generation within time range

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

sindhu_ibizcs
Contributor

Re: Date Generation within time range

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

sindhu_ibizcs
Contributor

Re: Date Generation within time range

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
Valued Contributor

Re: Date Generation within time range

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,

sindhu_ibizcs
Contributor

Re: Date Generation within time range

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
Valued Contributor

Re: Date Generation within time range

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

sindhu_ibizcs
Contributor

Re: Date Generation within time range

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