topic Re: Invalid Expression Error when trying to create a Calendar in QlikView

Invalid Expression Error when trying to create a Calendar

lucasdavis500 — Wed, 25 Nov 2020 16:16:04 GMT

I'm trying to load my "master table" twice in to my QVW, because I have more than one date I would like to use as a reference for the end user... however, I'm getting an error when trying to load the Calendar script : 'Invalid Expression'

Does anyone have any insight into what is causing this error?

The table I'm Resident Loading this data, Final_Data_Model2, is Qualified so that it doesn't tie directly back to Final_Data_Model.

Could this be causing an error?

I've also tried to add the following above Master_Calendar2:

UNQUALIFY Week_Res, Year_Res....WeekDay_Res ;

Error when reloading:

Invalid expression

MasterCalendar2:

LOAD

[Resolution Date],

WEEK([Resolution Date]) AS Week_Res,

YEAR([Resolution Date]) AS Year_Res,

MONTH([Resolution Date]) AS Month_Res,

DAY([Resolution Date]) As Day_Res,

YEARTODATE([Resolution Date])*-1 AS CurYTDFlag_Res,

YEARTODATE([Resolution Date],-1)*-1 AS LastYTDFlag_Res,

INYEAR([Resolution Date], MONTHSTART(MAX([Resolution Date])),-1) AS RC12_Res,

DATE(MONTHSTART([Resolution Date]), 'MMM-YYYY') AS MonthYear_Res,

APPLYMAP('QuartersMap2', MONTH([Resolution Date]), NULL()) AS Quarter_Res,

WEEK(WEEKSTART([Resolution Date])) & '-' & WEEKYEAR([Resolution Date]) AS WeekYear_Res,

WEEKDAY([Resolution Date]) AS WeekDay_Res

RESIDENT Final_Data_Model2

Script Looks Like:

MasterCalendar2:
LOAD
[Resolution Date],
WEEK([Resolution Date]) AS Week_Res,
YEAR([Resolution Date]) AS Year_Res,
MONTH([Resolution Date]) AS Month_Res,
DAY([Resolution Date]) As Day_Res,
YEARTODATE([Resolution Date])*-1 AS CurYTDFlag_Res,
YEARTODATE([Resolution Date],-1)*-1 AS LastYTDFlag_Res,
INYEAR([Resolution Date], MONTHSTART(MAX([Resolution Date])),-1) AS RC12_Res,
DATE(MONTHSTART([Resolution Date]), 'MMM-YYYY') AS MonthYear_Res,
APPLYMAP('QuartersMap2', MONTH([Resolution Date]), NULL()) AS Quarter_Res,
WEEK(WEEKSTART([Resolution Date])) & '-' & WEEKYEAR([Resolution Date]) AS WeekYear_Res,
WEEKDAY([Resolution Date]) AS WeekDay_Res
RESIDENT Final_Data_Model2;

STORE MasterCalendar2 INTO $(vQVDPath)MasterCalendar2.qvd (QVD);

Log Looks Like:

11/10/2016 11:46:15 AM: 0801 MasterCalendar2:

11/10/2016 11:46:15 AM: 0802 LOAD

11/10/2016 11:46:15 AM: 0803 [Resolution Date],

11/10/2016 11:46:15 AM: 0804 WEEK([Resolution Date]) AS Week_Res,

11/10/2016 11:46:15 AM: 0805 YEAR([Resolution Date]) AS Year_Res,

11/10/2016 11:46:15 AM: 0806 MONTH([Resolution Date]) AS Month_Res,

11/10/2016 11:46:15 AM: 0807 DAY([Resolution Date]) As Day_Res,

11/10/2016 11:46:15 AM: 0808 YEARTODATE([Resolution Date])*-1 AS CurYTDFlag_Res,

11/10/2016 11:46:15 AM: 0809 YEARTODATE([Resolution Date],-1)*-1 AS LastYTDFlag_Res,

11/10/2016 11:46:15 AM: 0810 INYEAR([Resolution Date], MONTHSTART(MAX([Resolution Date])),-1) AS RC12_Res,

11/10/2016 11:46:15 AM: 0811 DATE(MONTHSTART([Resolution Date]), 'MMM-YYYY') AS MonthYear_Res,

11/10/2016 11:46:15 AM: 0812 APPLYMAP('QuartersMap2', MONTH([Resolution Date]), NULL()) AS Quarter_Res,

11/10/2016 11:46:15 AM: 0813 WEEK(WEEKSTART([Resolution Date])) & '-' & WEEKYEAR([Resolution Date]) AS WeekYear_Res,

11/10/2016 11:46:15 AM: 0814 WEEKDAY([Resolution Date]) AS WeekDay_Res

11/10/2016 11:46:15 AM: 0815 RESIDENT Final_Data_Model2

11/10/2016 11:46:19 AM: 12 fields found: Resolution Date, MasterCalendar2.Week_Res, MasterCalendar2.Year_Res, MasterCalendar2.Month_Res, MasterCalendar2.Day_Res, MasterCalendar2.CurYTDFlag_Res, MasterCalendar2.LastYTDFlag_Res, MasterCalendar2.RC12_Res, MasterCalendar2.MonthYear_Res, MasterCalendar2.Quarter_Res, MasterCalendar2.WeekYear_Res, MasterCalendar2.WeekDay_Res,

11/10/2016 11:46:19 AM: Invalid expression

11/10/2016 11:46:19 AM: Error: Invalid expression

11/10/2016 11:46:23 AM: Execution finished.

Re: Invalid Expression Error when trying to create a Calendar

sunny_talwar — Thu, 10 Nov 2016 17:59:13 GMT

You are using a max function in your load which needs a group by statement. What exactly are you trying to do here?

Re: Invalid Expression Error when trying to create a Calendar

lucasdavis500 — Thu, 10 Nov 2016 18:15:44 GMT

So, I was trying to create a Master Calendar for my Resolution Date, but there are NULL Values in Resolution Date, which is causing my master calendar script to not work properly...

original script:

QuartersMap2:
MAPPING LOAD
ROWNO() AS Month_Res,
'Q' & CEIL (ROWNO()/3) AS Quarter_Res
AUTOGENERATE (12);

Temp2:
LOAD
MIN([Resolution Date]) AS MinDate_Res,
MAX([Resolution Date]) AS MaxDate_Res
RESIDENT Final_Data_Model2;

LET vMinDate = PEEK('MinDate_Res', 0, 'Temp');
LET vMaxDate = PEEK('MaxDate_Res', 0, 'Temp');
DROP TABLE Temp2;

TempCalendar:
LOAD
$(vMinDate) + ITERNO()-1 AS Num_Res,
DATE($(vMinDate) + ITERNO() - 1) AS TempDate_Res
AUTOGENERATE 1 WHILE $(vMinDate) + ITERNO() -1 <= $(vMaxDate);

TempCal2:
LOAD
date($(vMinDate) + rowno() - 1) AS TempDate_Res
AUTOGENERATE
$(vMaxDate) - $(vMinDate) + 1;

MasterCalendar:2
Load
TempDate_Res AS [Resolution Date],
WEEK(TempDate_Res) AS Week_Res,
YEAR(TempDate_Res) AS Year_Res,
MONTH(TempDate_Res) AS Month_Res,
DAY(TempDate_Res) As Day_Res,
YEARTODATE(TempDate_Res)*-1 AS CurYTDFlag_Res,
YEARTODATE(TempDate_Res,-1)*-1 AS LastYTDFlag_Res,
INYEAR(TempDate_Res, MONTHSTART($(vMaxDate)),-1) AS RC12,
DATE(MONTHSTART(TempDate_Res), 'MMM-YYYY') AS MonthYear_Res,
APPLYMAP('QuartersMap', MONTH(TempDate_Res), NULL()) AS Quarter_Res,
WEEK(WEEKSTART(TempDate_Res)) & '-' & WEEKYEAR(TempDate_Res) AS WeekYear_Res,
WEEKDAY(TempDate_Res) AS WeekDay_Res
RESIDENT TempCal2
ORDER BY TempDate_Res ASC;
DROP TABLE TempCal2;

STORE MasterCalendar2 INTO $(vQVDPath)MasterCalendar2.qvd (QVD);

so I scaled this down to:

QuartersMap2:
MAPPING LOAD
ROWNO() AS Month_Res,
'Q' & CEIL (ROWNO()/3) AS Quarter_Res
AUTOGENERATE (12);

//Temp2:
//LOAD
// MIN([Resolution Date]) AS MinDate_Res,
// MAX([Resolution Date]) AS MaxDate_Res
//RESIDENT Final_Data_Model2;
//
//LET varMinDate_Res = PEEK('MinDate_Res', 0, 'Temp2');
//LET varMaxDate_Res = PEEK('MaxDate_Res', 0, 'Temp2');
//DROP TABLE Temp2;
//
//TempCalendar2:
//LOAD
// $(varMinDate_Res) + ITERNO()-1 AS Num_Res,
// DATE($(varMinDate_Res) + ITERNO() - 1) AS TempDate_Res
// AUTOGENERATE 1 WHILE $(varMinDate_Res) + ITERNO() -1 <= $(varMaxDate_Res);
//
//TempCal2:
//LOAD
//date($(varMinDate_Res) + rowno() - 1) AS TempDate_Res
//AUTOGENERATE
// $(varMaxDate_Res) - $(varMinDate_Res) + 1;

MasterCalendar2:
LOAD
[Resolution Date],
WEEK([Resolution Date]) AS Week_Res,
YEAR([Resolution Date]) AS Year_Res,
MONTH([Resolution Date]) AS Month_Res,
DAY([Resolution Date]) As Day_Res,
YEARTODATE([Resolution Date])*-1 AS CurYTDFlag_Res,
YEARTODATE([Resolution Date],-1)*-1 AS LastYTDFlag_Res,
INYEAR([Resolution Date], MONTHSTART(MAX([Resolution Date])),-1) AS RC12_Res,
DATE(MONTHSTART([Resolution Date]), 'MMM-YYYY') AS MonthYear_Res,
APPLYMAP('QuartersMap2', MONTH([Resolution Date]), NULL()) AS Quarter_Res,
WEEK(WEEKSTART([Resolution Date])) & '-' & WEEKYEAR([Resolution Date]) AS WeekYear_Res,
WEEKDAY([Resolution Date]) AS WeekDay_Res
RESIDENT Final_Data_Model2;

STORE MasterCalendar2 INTO $(vQVDPath)MasterCalendar2.qvd (QVD);

replacing

INYEAR(TempDate_Res, MONTHSTART($(vMaxDate)),-1) AS RC12

with

INYEAR([Resolution Date], MONTHSTART(MAX([Resolution Date])),-1) AS RC12_Res

Re: Invalid Expression Error when trying to create a Calendar

sunny_talwar — Thu, 10 Nov 2016 18:18:49 GMT

NULL should not be causing issues, can you elaborate on the issues you are seeing due to the NULL in resolution date

Re: Invalid Expression Error when trying to create a Calendar

lucasdavis500 — Thu, 10 Nov 2016 18:32:06 GMT

My Error:

My Script:

QuartersMap2:
MAPPING LOAD
ROWNO() AS Month_Res,
'Q' & CEIL (ROWNO()/3) AS Quarter_Res
AUTOGENERATE (12);

Temp2:
LOAD
MIN([Resolution Date]) AS MinDate_Res,
MAX([Resolution Date]) AS MaxDate_Res
RESIDENT Final_Data_Model2;

LET vMinDate = PEEK('MinDate_Res', 0, 'Temp2');
LET vMaxDate = PEEK('MaxDate_Res', 0, 'Temp2');
DROP TABLE Temp2;

TempCalendar2:
LOAD
$(vMinDate) + ITERNO()-1 AS Num_Res,
DATE($(vMinDate) + ITERNO() - 1) AS TempDate_Res
AUTOGENERATE 1 WHILE $(vMinDate) + ITERNO() -1 <= $(vMaxDate);

TempCal2:
LOAD
date($(vMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(vMaxDate) - $(vMinDate) + 1;

MasterCalendar2:
Load
TempDate_Res AS [Resolution Date],
WEEK(TempDate_Res) AS Week_Res,
YEAR(TempDate_Res) AS Year_Res,
MONTH(TempDate_Res) AS Month_Res,
DAY(TempDate_Res) As Day_Res,
YEARTODATE(TempDate_Res)*-1 AS CurYTDFlag_Res,
YEARTODATE(TempDate_Res,-1)*-1 AS LastYTDFlag_Res,
INYEAR(TempDate_Res, MONTHSTART($(vMaxDate)),-1) AS RC12_Res,
DATE(MONTHSTART(TempDate_Res), 'MMM-YYYY') AS MonthYear_Res,
APPLYMAP('QuartersMap', MONTH(TempDate_Res), NULL()) AS Quarter_Res,
WEEK(WEEKSTART(TempDate_Res)) & '-' & WEEKYEAR(TempDate_Res) AS WeekYear_Res,
WEEKDAY(TempDate_Res) AS WeekDay_Res
RESIDENT TempCal2
ORDER BY TempDate_Res ASC;
DROP TABLE TempCal2;

STORE MasterCalendar2 INTO $(vQVDPath)MasterCalendar2.qvd (QVD);

MY LOG:

11/10/2016 12:24:08 PM: 0772 QuartersMap2:

11/10/2016 12:24:08 PM: 0773 MAPPING LOAD

11/10/2016 12:24:08 PM: 0774 ROWNO() AS Month_Res,

11/10/2016 12:24:08 PM: 0775 'Q' & CEIL (ROWNO()/3) AS Quarter_Res

11/10/2016 12:24:08 PM: 0776 AUTOGENERATE (12)

11/10/2016 12:24:08 PM: 2 fields found: Month_Res, Quarter_Res, 12 lines fetched

11/10/2016 12:24:08 PM: 0778 Temp2:

11/10/2016 12:24:08 PM: 0779 LOAD

11/10/2016 12:24:08 PM: 0780 MIN([Resolution Date]) AS MinDate_Res,

11/10/2016 12:24:08 PM: 0781 MAX([Resolution Date]) AS MaxDate_Res

11/10/2016 12:24:08 PM: 0782 RESIDENT Final_Data_Model2

11/10/2016 12:24:11 PM: 2 fields found: Temp2.MinDate_Res, Temp2.MaxDate_Res, 1 lines fetched

11/10/2016 12:24:11 PM: 0784 LET vMinDate = PEEK('MinDate_Res', 0, 'Temp')

11/10/2016 12:24:11 PM: 0785 LET vMaxDate = PEEK('MaxDate_Res', 0, 'Temp')

11/10/2016 12:24:11 PM: 0786 DROP TABLE Temp2

11/10/2016 12:24:11 PM: 0788 TempCalendar2:

11/10/2016 12:24:11 PM: 0789 LOAD

11/10/2016 12:24:11 PM: 0790 + ITERNO()-1 AS Num_Res,

11/10/2016 12:24:11 PM: 0791 DATE( + ITERNO() - 1) AS TempDate_Res

11/10/2016 12:24:11 PM: 0792 AUTOGENERATE 1 WHILE + ITERNO() -1 <=

11/10/2016 12:24:11 PM: Error: Field not found - <<=>

11/10/2016 12:25:30 PM: General Script Error

11/10/2016 12:25:30 PM: Execution Failed

11/10/2016 12:25:30 PM: Execution finished.

DEBUGGER:

LET vMinDate = PEEK('MinDate_Res', 0, 'Temp2');
LET vMaxDate = PEEK('MaxDate_Res', 0, 'Temp2');

RETURNS <NULL>

I think I've found my issue though. [Resolution Date] contains the actual TEXT, 'Null'

I believe this is the issue..

Re: Invalid Expression Error when trying to create a Calendar

lucasdavis500 — Thu, 10 Nov 2016 19:05:09 GMT

Question, how long does the QUALIFY statement qualify for?

Does it stop after it reaches the end of a table?

It seems like QUALIFY *; before one of my tables is trying to QUALIFY all TABLES that come after the statement QUALIFY *;

Re: Invalid Expression Error when trying to create a Calendar

sunny_talwar — Thu, 10 Nov 2016 19:21:47 GMT

It doesn't unless you specifically put UNQUALIFY *; at the end of the table