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

Calendar

I'm having trouble getting a calendar to work in my model.

I have the script for making a calendar from a previous model, but I think there are some changes I need to make to the scrpt that I am overlooking.

Here is the script I have for the calendar:

//*************** Get Dates from Data ***************

DateTemp:

LOAD Date(Date#(Timestamp,'YYYYMMDD'),'DD/MM/YYYY') as Timestamp

RESIDENT CallLogs

ORDER BY Timestamp Asc;

LET vMinDate = Num(Peek('Timestamp', 0, 'DateTemp'));  //LET ... evaluates the expression on the right side of the ’=’ before it is assigned to the variable

LET vMaxDate = Num(Peek('Timestamp', -1, 'DateTemp'));    

LET vToday = Num(today());

//*************** Temporary Calendar ***************

TempCalendar:

LOAD

$(vMinDate)+IterNo()-1 AS Num,

Date($(vMinDate)+IterNo()-1) AS TempDate

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

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

MasterCalendar:

LEFT KEEP (CallLog)

LOAD TempDate AS %_DateKey,

  TempDate as Date,

     week(TempDate) AS Week,

     year(TempDate) AS Year,

     month(TempDate) AS Month,

     day(TempDate) AS Day,

     weekday(TempDate) AS WeekDay,

     'Q' & ceil(month(TempDate)/3) as Quarter,

     date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

     week(TempDate)&'-'&Year(TempDate) AS WeekYear,

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

     Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag,

     num(month(TempDate)) as Period

RESIDENT TempCalendar 

ORDER BY TempDate Asc;

Drop table TempCalendar;

Drop table DateTemp;

9 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     I think you have to change this TempDate as Date to TempDate as Timestamp then you can able to join.


MasterCalendar:

LEFT KEEP (CallLog)

LOAD TempDate AS %_DateKey,

  TempDate as Date,

     week(TempDate) AS Week,

     year(TempDate) AS Year,

     month(TempDate) AS Month,

     day(TempDate) AS Day,

     weekday(TempDate) AS WeekDay,

     'Q' & ceil(month(TempDate)/3) as Quarter,

     date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

     week(TempDate)&'-'&Year(TempDate) AS WeekYear,

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

     Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag,

     num(month(TempDate)) as Period

RESIDENT TempCalendar

ORDER BY TempDate Asc;

Celambarasan

Anonymous
Not applicable
Author

i changed

TempDate as Date

to

TempDate as Timestamp

BUT i still get the following error messages when Reloading:

Field not found

DateTemp:

LOAD Date(Date#(Timestamp,'YYYYMMDD'),'DD/MM/YYYY') as Timestamp

RESIDENT CallLogs

ORDER BY Timestamp Asc

Field not found - <<=>

TempCalendar:

LOAD

+IterNo()-1 AS Num,

Date(+IterNo()-1) AS TempDate

AUTOGENERATE 1 WHILE (+IterNo()-1) <=

Table not found

MasterCalendar:

LEFT KEEP (CallLog)

LOAD TempDate AS %_DateKey,

  TempDate as Timestamp,

     week(TempDate) AS Week,

     year(TempDate) AS Year,

     month(TempDate) AS Month,

     day(TempDate) AS Day,

     weekday(TempDate) AS WeekDay,

     'Q' & ceil(month(TempDate)/3) as Quarter,

     date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

     week(TempDate)&'-'&Year(TempDate) AS WeekYear,

     Year2Date(TempDate, 0, 1, 40966)*-1 AS CurYTDFlag,

     Year2Date(TempDate,-1, 1, 40966)*-1 AS LastYTDFlag,

     num(month(TempDate)) as Period

RESIDENT TempCalendar 

ORDER BY TempDate Asc

Table not found

DROP TABLES statement

Table not found

DROP TABLES statement


Not applicable
Author

Hi,

can you paste code for your CallLogs table?

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     Did you have Timestamp field in CallLogs table?Problem came because of that only.

Celambarasan

Anonymous
Not applicable
Author

//-------------------------------------------------------------

//This is the CallLogs data from the CallLogs QVD

//-------------------------------------------------------------

CallLogs:

LOAD agentcode,

     agentname,

     callduration,

     callid,

     id,

     profileno,

     ramsno,

     statedescription,

     timestamp

FROM

C:\Users\Chris\TRAINING\CustomerCallLogs\QVDs\CallLogs.qvd

(qvd);

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     Check with this for DateTemp table.

    

DateTemp:

LOAD Date(Date#(timestamp,'YYYYMMDD'),'DD/MM/YYYY') as TimeStamp

RESIDENT CallLogs

ORDER BY TimeStampAsc

Celambarasan

Not applicable
Author

Try,

DateTemp:

LOAD Date(Date#(timestamp,'YYYYMMDD'),'DD/MM/YYYY') as timestamp

RESIDENT CallLogs

ORDER BY timestamp asc;

LET vMinDate = Num(Peek('timestamp', 0, 'DateTemp'));  //LET ... evaluates the expression on the right side of the ’=’ before it is assigned to the variable

LET vMaxDate = Num(Peek('timestamp', -1, 'DateTemp'));    

LET vToday = Num(today());

mdmukramali
Specialist III
Specialist III

I think spell mistake

LEFT KEEP (callLogs)

thanks,

Mukram

mdmukramali
Specialist III
Specialist III

and

in callLogs timestamp, so use t small letter in DateTemp Table.