topic Re: Error in expression in QlikView

Error in expression

Thu, 23 Mar 2017 07:07:46 GMT

Hi everyone,

I got stuck in a Master Calendar creation and have been for days trying to fix it unsuccessfully. Please, some one could give a hand to go through it?

I guess the error is in the inyeartodate statement. But couldnt fix it.

MinMax:

LOAD

Min(OrderDate) as MinDate,

Max(OrderDate) as MaxDate

Resident Orders;

LET vMindDate = Num(Peek('MinDate', 0, 'MinxMax'));

LET vMaxDate = Num(Peek('MinDate', 0, 'MinMax'));

LET vToday = $(VMaxDate);

//***************************** Temporary Calendar *****************************

TempCal:

LOAD

date($(vMindDate) + rowno() - 1) AS TempDate

AutoGenerate $(vMaxDate) - $(vMindDate) +1;

DROP TABLE MinMax;

//***************************** Master Calendar *****************************

MasterCalendar:

LOAD

TempDate as OrderDate,

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;

ERROR:

Error in expression:

')' expected

MasterCalendar:

LOAD

TempDate as OrderDate,

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 ASC;

Re: Error in expression

ahaahaaha — Thu, 23 Mar 2017 11:32:19 GMT

Hi Pedro,

Look here (attached file, page 5).

Regards,

Andrey

Re: Error in expression

sunny_talwar — Thu, 23 Mar 2017 11:36:18 GMT

Your vToday is incorrectly defined

LET vToday = $(VMaxDate); -> Should be vMaxDate

But, why do you need vToday? Use vMaxDate instead:

MasterCalendar:

LOAD

TempDate as OrderDate,

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, $(vMaxDate), 0) * -1 AS CurYTDFlag,

InYearToDate(tempDate, $(vMaxDate), -1)* -1 AS LastYTDFlag

Resident TempCal

ORDER By TempDate ASC;

DROP TABLE TempCal;

Re: Error in expression

sumit_ranjan_pa — Thu, 23 Mar 2017 11:48:24 GMT

HI Pedro,

There are couple of typo in the section against tempDate.

In the Day function the field is tempDate, so in the third to last row.

Please refrain from using variables in the load statement as they are directly replaced with values which sometimes create errors.

Your vToday is incorrectly defined as well.

PFB the statement i created.

MasterCalendar:

LOAD

TempDate as OrderDate,

Week(TempDate) as Week,

year(TempDate) as Year,

Month(TempDate) AS Month,

DayName(TempDate) AS Day,

WeekDay(TempDate) AS WeekDay,

'Q' & ceil(month(TempDate)/3) AS Quarter,

Date(MonthStart(TempDate), 'MMM-YYYY') AS MonthYear,

Week(weekstart(TempDate))&'-'& Year(TempDate) AS WeekYear,

YearToDate(TempDate) * -1 AS CurYTDFlag,

//inYearToDate(TempDate,$(vToday), 0) * -1 AS CurYTDFlag,

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

//inYearToDate(TempDate, $(vToday), -1)* -1 AS LastYTDFlag

Resident TempCal ORDER By TempDate ASC;

DROP TABLE TempCal;

Thanks and regards,

Sumit