topic Re: Error In Load Script in App Development

Error In Load Script

Anonymous — Tue, 14 Nov 2017 07:21:12 GMT

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!

Re: Error In Load Script

petter — Tue, 14 Nov 2017 07:34:15 GMT

You should use RowNo() instead of IterNo() when you have an autogenerate.

http://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/CounterFunctions/IterNo.htm

IterNo() only works if you have a WHILE clause in your load staement.

Re: Error In Load Script

Anonymous — Tue, 14 Nov 2017 07:37:30 GMT

Thanks for your reply!

I changed it to RowNo(); however, the same issue exists!

Any ideas?

Re: Error In Load Script

Anonymous — Tue, 14 Nov 2017 07:38:02 GMT

By the way, I am using Qlik Sense!

Re: Error In Load Script

Gysbert_Wassenaar — Tue, 14 Nov 2017 07:40:27 GMT

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.

Re: Error In Load Script

Anonymous — Tue, 14 Nov 2017 07:46:00 GMT

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

Re: Error In Load Script

Gysbert_Wassenaar — Tue, 14 Nov 2017 12:58:40 GMT

Same error, same cause. Make sure your variable $(varMaxDate) gets a correct value. Can you post a small example qvw that demonstrates the issue?

Re: Error In Load Script

shraddha_g — Tue, 14 Nov 2017 14:32:49 GMT

$(varMaxDate) is giving wrong value