Error in expression ")" expected Master Calendar

[paulyeo11]

Hi All

i get the below error msg :-

Error in expression:

')' expected

MasterCalendar:

LOAD TempDate AS date,

          D AS link_Date,

          D AS Date,

          If(Num([TempDate]) >=  and Num([TempDate]) < , -1, 0)                               As LY_YTD

RESIDENT TempCalendar

ORDER BY TempDate ASC

when i run below script :-

MasterCalendar:

LOAD TempDate AS date,

          D AS link_Date,

          D AS Date,

          If(Num([TempDate]) >= $(vYearStartLY) and Num([TempDate]) < $(vMonthNowLY), -1, 0)                               As LY_YTD

RESIDENT TempCalendar

ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

can some one tell me what wrong with the script above ?

Paul

[Sokkorn · Accepted solution]

Hi Paul,

I think it cause from this line

If(Num([TempDate]) >=  and Num([TempDate]) < , -1, 0)  As LY_YTD -> Wrong syntax

Maybe this you try this one

If(Num([TempDate]) >=  Num([TempDate]), -1, 0)  As LY_YTD

Regards,

Sokkorn

[paulyeo11]

Hi Sok

yes you are right , once i remove the line , it work. i keep supect due to the top line.

Paul

[#4]

Your vYearStartLY and vMonthNowLY variables are empty at the point in which they are used in that script, that's why the load statement in your error message is missing values like below:

If(Num([TempDate]) >=  and Num([TempDate]) < , -1, 0)                               As LY_YTD

I imagine that line should read something like:

If(Num([TempDate]) >= 40210 and Num([TempDate]) < 40300 , -1, 0)                               As LY_YTD

I suggest you use the debug method to run through your script if you think that those variables should be declared and have data when you get to that load statement.

[paulyeo11]

Hi Nigel west

when i try the debuting tool , it still stop at the line below :-

MasterCalendar:

then how can you know that is cause by below line problem ?

          If(Num([TempDate]) >= $(vYearStart) and Num([TempDate]) < $(vMonthNow), -1, 0)                                         As YTD,

Actual fact my issue is i need to declare vYearStart

Paul

[#6]

Paul

I KNOW, 100% FACT, that is where the problem is, I know this because in your original message you added an extract of the error message, and that error message shows you exactly where the problem is,

Your original error message:

Error in expression:

')' expected

MasterCalendar:

LOAD TempDate AS date,

          D AS link_Date,

          D AS Date,

          If(Num([TempDate]) >=  and Num([TempDate]) < , -1, 0)                               As LY_YTD

RESIDENT TempCalendar

ORDER BY TempDate ASC

All you need to do is read that line, and then compare it to your load script which suggests that you want to compare TempDate to two different values. You can clearly see from the above that it is not including those values.

The conclusion therefore is that those variables are empty, they are NULL, they don't have a value!

You can of course remove the line from your code and that will fix it, but that doesn't give you your LY_YTD value which I assumed you wanted, because if you didn't want it then why on earth would you add the line in in the first place.

[paulyeo11]

Hi Nigel

Okay after i add those variable vSomething and it work fine. So now i understand , the debuging will stop some where near the problem line.

Thank you, first time i use debuging tool

Paul