topic Master Calendar Code with CSV file script line error in QlikView

Master Calendar Code with CSV file script line error

Wed, 05 Mar 2014 06:18:36 GMT

I have used this code before with no problem, however the only difference is thatIi'm now using a CSV file with a field that has a Date in the format of 01JAN2014:00:00:00.

Should I be using something different when trying to create a master calendar off of a CSV?

Now i'm trying to use it in a new document and am getting the following errors:

Script line error:

TempCal:

LOAD

date(+rowno()-1 ) AS TempDate

AutoGenerate

- +1

as well as:

Error in expression:

')' expected

MasterCalendar:

LOAD Distinct

TempDate AS [Case 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,

InyearToDate(TempDate, ,0)*-1 As CurYTDFlag,

InYearToDate(TempDate, ,-1)*-1 AS LastYTDFlag

Resident

TempCal

Order By TempDate A

Here is my Master Calendar Creation Code:

// Call Master Calendar

MinMax:
LOAD
NUM ( MIN ( [case_created_date] )) AS MinDate,
NUM ( MAX ( [case_created_date] )) AS MaxDate
RESIDENT
AGG_CASES
;

// peek( fieldname [, row [, tablename ] ] )

LET vMinDateb = NUM(Peek ( 'MinDate', 0, 'MinMax' ));
LET vMaxDateb = NUM(Peek ( 'MaxDate', -1, 'MinMax' ));
LET vTodayb = $(vMaxDateb);

//**************************** Temporary Calendar - CALL ****************************

TempCal:
LOAD
date($(vMinDateb)+rowno()-1 ) AS TempDate
AutoGenerate
$(vMaxDateb) - $(vMinDateb) +1;

DROP TABLE MinMax;

MasterCalendar:
LOAD Distinct
TempDate AS [Case 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,
InyearToDate(TempDate, $(vToday),0)*-1 As CurYTDFlag,
InYearToDate(TempDate, $(vToday),-1)*-1 AS LastYTDFlag
Resident
TempCal
Order By TempDate ASC;

DROP TABLE TempCal;

Re: Master Calendar Code with CSV file script line error

tresB — Wed, 05 Mar 2014 06:35:19 GMT

Script line error:

TempCal:

LOAD

date(+rowno()-1 ) AS TempDate

AutoGenerate

- +1

as well as:

Error in expression:

')' expected

Are these '+','-' symbols intentional? Apart from that you are missing one semicolon (;) at the end. So try like:

TempCal:

LOAD

date(rowno()-1 ) AS TempDate

AutoGenerate 1;

Re: Master Calendar Code with CSV file script line error

jagan — Wed, 05 Mar 2014 06:36:38 GMT

Hi,

Can you attach some sample data?

Regards,

jagan.

Re: Master Calendar Code with CSV file script line error

jagan — Wed, 05 Mar 2014 06:45:18 GMT

Hi,

I think the date format is the issue, check the vMinDateb while loading it is empty. Replace below code with your code and check.

MinMax:

LOAD

NUM ( MIN (Timestamp#([case_created_date], 'DDMMMYYYY:hh:mm:ss') )) AS MinDate,

NUM ( Max(Timestamp#([case_created_date], 'DDMMMYYYY:hh:mm:ss') )) AS MaxDate

RESIDENT

AGG_CASES;

Regards,

Jagan.

Re: Master Calendar Code with CSV file script line error

Wed, 05 Mar 2014 16:51:07 GMT

Thanks Jagan!

It was the formatting issue.

Paul