Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.


Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Where clause

Hi All,

I've encountered a problem using Last YTD flags in a 4-4-5 calendar. Alignment of dates needs to be based on the day in period for accurate comparsions. I've resolved myself to replace the current calendar with a calendar built in the Business system because I can't seem to find a way to include the day in period in my old calendar creation.

Plan B calls for creating a daily calendar on the business system exploding from/to dates and establishing the day within the period for each date. In the load script I'm setting up the YEAR||Period||day in period to form a test in order to set the FlagLastYTD aligned with the current day in period for this year.

Everything would fall in place if the calendar load in the script would be limited to the last invoice date.

My problem is the placement of the where clause in the attached example.

When I change the script to

[..\Documents\Data\Date context.xls]

(biff, embedded labels, table is [Gened Table$])WHERE([Calendar Date] <= '$(vMaxDate)');

I get:

Error in expression:

')' expected

In the Calendar load

Any help would be appreciated.


Rich DeRocco

3 Replies
Not applicable

Re: Where clause

Don't know if this matters but this should be all you need

WHERE [Calendar Date]<='$(vMaxDate)';

Not even sure you need the string markers there but just try it both ways.  You don't need the parenthesis after WHERE but I'm not sure why it matters.  Also put WHERE on the next line... it's touching the other ')'.  Not sure if that matters either.. but give it a shot.

My guess is that the problem is that the end parenthesis in the "table is [whatever])" and the WHERE clause don't have a space between them.

Not applicable

Where clause


it has to do with the date comparizons. Your variable vMaxDate may contain either the formatted date string or the numeric part, but not both (variables don't get the priviledge of being dual). It depends on how you assigned it.

Try playing with the condition:

- without quotes, using formatted string?

- without quotes, using numeric values?

I think Excel should work well with the numeric values, since it's using the same calculation formula for dates...


Not applicable

Where clause

Thanks to both Trent and Oleg,

Trent for the syntax pointer and Oleg for the comparison values.

It is working now.