topic Where clause in LOAD in QlikView

Where clause in LOAD

Tue, 13 Apr 2010 14:16:09 GMT

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

Where clause in LOAD

justinasp — Tue, 13 Apr 2010 14:22:11 GMT

I guess you want something like this:

WHERE YEAR([sales]) = 2008

Cheers,

Justinas

Where clause in LOAD

Tue, 13 Apr 2010 14:28:56 GMT

Hi Justinas

I get the message "garbage after statement"

I wrote the load like this

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

Where clause in LOAD

Tue, 13 Apr 2010 14:46:55 GMT

Hi,

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

LOAD sales

FROM ... / RESIDENT ...

WHERE YEAR([Sales]) = 2008

Where clause in LOAD

tabletuner — Tue, 13 Apr 2010 14:59:27 GMT

Hi Polschou,

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

SET DateFormat='DD-MM-YYYY';

Regards,

Tjeerd

Where clause in LOAD

johnw — Wed, 14 Apr 2010 00:10:55 GMT

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
;

Where clause in LOAD

Wed, 14 Apr 2010 08:09:13 GMT

Yes they are the same format

Where clause in LOAD

Wed, 14 Apr 2010 08:18:02 GMT

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

Where clause in LOAD

justinasp — Wed, 14 Apr 2010 11:40:08 GMT

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

Where clause in LOAD

Miguel_Angel_Baeyens — Wed, 14 Apr 2010 12:37:49 GMT

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.

Where clause in LOAD

Wed, 14 Apr 2010 13:27:32 GMT

Hi Miguel

It wont load properly.
What I´m trying to do is(I have made another post about it but I dont get enything I can use) is to show an increase in sold items in year 2008 and 2009.

I got one list with all the sales dates representing 1 sold item per date.
Showing the frequency in my list give me the total of dates in 2008 and 2009.

2008, 1000 dates and 2009 2000 dates so its an increase of dates of 1000 or 100% increase, but how do I make the script
do thsi for me?