Where clause in LOAD

[#1]

IS it possible(I cant make it work) to put in a WHERE clause in the load sequence - somthing like this:

LOAD
left([sales],4) as lastyear
WHERE [sales] = 2008.

the sales list is a column with dates in this format dd-mm-yyyy

[justinasp]

I guess you want something like this:

WHERE YEAR([sales]) = 2008

Cheers,

Justinas

[#3]

Hi Justinas

I get the message "garbage after statement"

I wrote the load like this

Load
sales where YEAR([sales]) = 2008,

[#4]

Hi,

your are missing the FROM or RESIDENT part in your Statement.

LOAD sales

FROM ... / RESIDENT ...

WHERE YEAR([Sales]) = 2008

[tabletuner]

Hi Polschou,

Are your system variables on the main tabblad in the same format, for example:

SET DateFormat='DD-MM-YYYY';

Regards,

Tjeerd

[johnw]

I'm reading the question a little differently. To me, it looks like you want to do something like this?

LOAD
some fields
,left([sales],4) as lastyear where [sales] = 2008
,some other fields
from your source
;

If so, then you do it with an IF statement instead of a WHERE statement:

LOAD
some fields
,if([sales]=2008,left([sales],4)) as lastyear
,some other fields
from your source
;

[#7]

Yes they are the same format

[#8]

My script starts with setting the variables month dates and so on
After this I make an ODBC connection to a access database.

[Timefaktor]:
ODBC CONNECT TO [MS Access-database; path to database];

SQL SELECT *
FROM my database.mdb;

LOAD
[sales]
left ([sales],4) as year
RESIDENT [Timefaktor];

Here is where i want to make a load with af WHERE clause

LOAD
[sales]
left ([sales],4) as lastyear where [sales] = 2008 (but it failes with a syntax error)
RESIDENT [Timefaktor];

[justinasp]

I'm still not very clear what you want to achieve. If you need to load only lines, where date contains year 2008, then you should use something like:

[TimeFactor]:
LOAD makedate(v1,v2,v3) as date, vField INLINE
[
v1, v2, v3, vField
2008,1,2, AAA
2009,2,5, BBB
2008,3,18, CCC
];
NOCONCATENATE
[AnotherTable]:
LOAD date,
 vField
RESIDENT [TimeFactor]
WHERE YEAR(date)=2008;
DROP TABLE [TimeFactor];

Other case clearify your task. Good luck,

Justinas

[Miguel_Angel_Baeyens]

Hello,

Following John Witherspoon's aproach, which seems good for me, I'd suggest you to try something like

ODBC CONNECT TO ...[TimeFaktor]:LOAD [sales] ,left([sales], 4) as lastyearWHERE [sales] = '2008';SQL SELECT *FROM yourdatabase.mdb;

I don't know how this will impact on performance, if any. It may be clearer for you to understand.