Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone!
I am trying to make a strigh forward forecast chart, and I have been googling and I found someone talking about creating a master calender and how to perform a simple linger regression; however, I am stuck at, what I believe is, a weird error. Please find below my script and the error associated with it:
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
Date(min(MYDATEFIELD)) as minDate, //Enter YourDateField from your data table
Date(max(MYDATEFIELD)+90) as maxDate //Generate dates until 3 months from max date for regression
Resident MYTABLE;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + (IterNo()-1) As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 500; //1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); (this was giving an error until I changed it to 500)
MasterCalendar:
Load
TempDate AS MYDATEFIELD, //This field is the key that connects to the data table
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, //This will be your dimension in the line-chart
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
and here is the error:
The following error occurred:
Unexpected token: ')', expected one of: '(', ',', 'ZTestw_z', 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'not', 'bitnot', ...
The error occurred here:
MasterCalendar: Load TempDate AS MYDATEFIELD, week(TempDate) As Week, Year(TempDate) As Year, Month(TempDate) As Month, Day(TempDate) As Day, YeartoDate(TempDate)*-1 as CurYTDFlag, YeartoDate(TempDate,-1)*-1 as LastYTDFlag, inyear(TempDate, Monthstart(>>>>>>)<<<<<<,-1) as RC12, date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, WeekDay(TempDate) as WeekDay Resident TempCalendar Order By TempDate ASC
Your help is really appreciated!
You should use RowNo() instead of IterNo() when you have an autogenerate.
IterNo() only works if you have a WHILE clause in your load staement.
Thanks for your reply!
I changed it to RowNo(); however, the same issue exists!
Any ideas?
By the way, I am using Qlik Sense!
Monthstart(>>>>>>)<<<<<<,-1
The variables don't get proper values. Try using numeric values instead of Date values:
Temp:
Load
Num(min(MYDATEFIELD)) as minDate, //Enter YourDateField from your data table
Nume(max(MYDATEFIELD)+90) as maxDate //Generate dates until 3 months from max date for regression
Resident MYTABLE;
Only in the master table apply the date format to TempDate get the correct MYDATEFIELD values. The While loop should work too when your variables have numeric values.
Thanks for your reply!
I did; however the same error exists.
Here is the new Script
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
(min(MYDATEFIELD)) as minDate, //Enter YourDateField from your data table
(max(MYDATEFIELD)+90) as maxDate //Generate dates until 9 months from max date for regression
Resident MYTABLE;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + (RowNo()-1) As Num,
Date($(varMinDate) + RowNo() - 1) as TempDate
AutoGenerate 500;//1 While $(varMinDate) + RowNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS MYDATEFIELD, //This field is the key that connects to the data table
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart(Date($(varMaxDate))),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, //This will be your dimension in the line-chart
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
here is the error:
TempCalendar << AUTOGENERATE(500) Lines fetched: 500
The following error occurred:
Unexpected token: ')', expected one of: ',', 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', 'like', ...
The error occurred here:
MasterCalendar: Load TempDate AS STG_FS_APPLICATIONSUPPORT.GCONTRACTDATE, week(TempDate) As Week, Year(TempDate) As Year, Month(TempDate) As Month, Day(TempDate) As Day, YeartoDate(TempDate)*-1 as CurYTDFlag, YeartoDate(TempDate,-1)*-1 as LastYTDFlag, inyear(TempDate, Monthstart(Date()>>>>>>)<<<<<<,-1) as RC12, date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, WeekDay(TempDate) as WeekDay Resident TempCalendar Order By TempDate ASC
Same error, same cause. Make sure your variable $(varMaxDate) gets a correct value. Can you post a small example qvw that demonstrates the issue?
$(varMaxDate) is giving wrong value