Error in expression

[#1]

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;

[ahaahaaha]

Hi Pedro,

Look here (attached file, page 5).

Regards,

Andrey

[sunny_talwar · Accepted solution]

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;

[sumit_ranjan_pa]

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