topic Calendar in QlikView

Calendar

Anonymous — Mon, 27 Feb 2012 07:57:06 GMT

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;

Calendar

CELAMBARASAN — Mon, 27 Feb 2012 08:47:14 GMT

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

Calendar

Anonymous — Mon, 27 Feb 2012 09:34:51 GMT

i changed

TempDate as Date

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

Calendar

Mon, 27 Feb 2012 09:42:44 GMT

Hi,

can you paste code for your CallLogs table?

Calendar

CELAMBARASAN — Mon, 27 Feb 2012 09:46:36 GMT

Hi,

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

Celambarasan

Calendar

Anonymous — Mon, 27 Feb 2012 09:48:14 GMT

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

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

Calendar

CELAMBARASAN — Mon, 27 Feb 2012 09:50:10 GMT

Hi,

Check with this for DateTemp table.

DateTemp:

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

RESIDENT CallLogs

ORDER BY TimeStampAsc

Celambarasan

Calendar

Mon, 27 Feb 2012 09:52:58 GMT

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

Calendar

mdmukramali — Mon, 27 Feb 2012 09:57:23 GMT

I think spell mistake

LEFT KEEP (callLogs)

thanks,

Mukram

Calendar

mdmukramali — Mon, 27 Feb 2012 10:00:29 GMT

and

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