Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
qvhelpplease
New Contributor II

master calendar with same formats as in sever

rwunderlichswuehlmrkachhiaimpgwassenaar i am trying to build a master calendar but failing as the time stamp format  in the sever  and the calendar which i am trying to build is in different formats , i request you to help me to build a master calender so that i can extract day ,time , hour , week , month,month name and all possible fields from it

7 Replies
ajsjoshua
Contributor III

Re: master calendar with same formats as in sever

Hi,

Try this in your script

/********************mastercalendar**********************/

MinMax:

LOAD

  MIN(Yourdatefield) AS MinDate,

  MAX(Yourdatefield) AS MaxDate

RESIDENT tablename;

LET vMinDate = NUM(PEEK('MinDate',0,'MinMax'));

LET vMaxDate = NUM(PEEK('MaxDate',0,'MinMax'));

LET vToday = $(vMaxDate);

//*****Temp Calendar*****

TempCal:

LOAD

  DATE($(vMinDate) + ROWNO() -1) AS TempDate

AUTOGENERATE

  $(vMaxDate) - $(vMinDate) + 1;

DROP TABLE MinMax;

//*****Master Calendar*****

MasterCalendar:

LOAD

TempDate AS Yourdatefield,

WEEK(TempDate) AS Week,

YEAR(TempDate) AS Year,

MONTH(TempDate) AS Month,

DAY(TempDate) AS Day,

WEEKDAY(TempDate) AS WeekDay,

AutoNumber(YEAR(TempDate) & MONTH(TempDate), 'MonthID') as [MonthID],

'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2 AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2)))  AS Quarter,

date(yearstart(TempDate,1,8),'YYYY') &' - '& 'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2 AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2))) as YearQuarter,

AutoNumber(date(yearstart(TempDate,1,8),'YYYY') &' - '& 'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2 AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2))),'QuarterID') as QuarterID,

DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS  MonthYear,

WEEK(TempDate) & '-' & YEAR(TempDate) AS WeekYear,

date(yearstart(TempDate,1,8),'YYYY') AS YearFiscal,

//INYEARTODATE(TempDate,$(vToday),0,8) * -1 AS CurYTDFlag,

//INYEARTODATE(TempDate,$(vToday),-1,1) * -1 AS LastYTDFlag

INYEARTODATE(TempDate,$(vToday),0,8) * -1 AS CurYTDFlag,

INYEARTODATE(TempDate,$(vToday),-1,8) * -1 AS LastYTDFlag

//INYEARTODATE(TempDate,TempDate,0,8) * -1 AS CurYTDFlag,

//INYEARTODATE(TempDate,TempDate,-1,8) * -1 AS LastYTDFlag

RESIDENT TempCal

ORDER BY TempDate ASC;

DROP TABLE TempCal;

qvhelpplease
New Contributor II

Re: master calendar with same formats as in sever

please do look at the attached file , the fields i am derviving is not getting associations with the the other fields

Re: master calendar with same formats as in sever

Hi,

There is problem with you Date format. You can change format in the Environment variable, or you need to do the change in date / timestamp function..

try to change like this..

Capture.JPG

qvhelpplease
New Contributor II

Re: master calendar with same formats as in sever

I TRIED IT STILL NOT WORKING ,CAN YOU PLEASE SEND THE APP

Re: master calendar with same formats as in sever

Can you post the excel sample?

qvhelpplease
New Contributor II

Re: master calendar with same formats as in sever

please find the sample

Re: master calendar with same formats as in sever

Hi,

May be check the attachment..

Calendar with time (timestamp)

Community Browser