topic Conditions in the script and filters in QlikView

Conditions in the script and filters

Tue, 31 May 2011 12:58:50 GMT

Sorry for another newcomer question, but I could not find this when I searched.

I have a lot of old data that is no longer relevant.

I would like to only load or show records with a date in 2010 or 2011.

Question no1:

Can I do this when I load the data? Can I do it in the SELECT statement and what would be the correct syntax (SQL?)?

(kind of Select * from SALES where SALES_DATE > 2009-12-31, but this does syntax does not seem to work)

Question no 2:

How can I filter in a Listbox so that it only shows some values? For example, if I want a SALES_DATE listbox to only show the dates for 2010 and 2011, so that people can not pick something older that this? Is it possible?

These are probably really basic questions, and it bugs me that I do not find an answer.

Conditions in the script and filters

erichshiino — Tue, 31 May 2011 13:13:40 GMT

For the first question, SQL Syntax could be like this:

Select * from SALES where SALES_DATE > '2009-12-3' if that's your correct date format.

or Select * from SALES where year(SALES_DATE)>=2010

for the second question, you could use and expression instead of a field. Then, your expression could be something like this:

= if( SALES_DATE>=makedate(2010), SALES_DATE)

The problem is: if you create a date-based chart, this expression on a list will not prevent the data from showing on the chart for 2009, for example. You would have to change all the expressions for that.

It`s better to solve it with question 1, because you application will use less memory and have better performance.

Hope it helps,

Erich

Conditions in the script and filters

Tue, 31 May 2011 13:53:16 GMT

I must be doing something wrong, because it does not let me put the condition there.

I tried to put a condition on another field (DOKNR), just to sure it is not a date-format isssue, but it does not work. What am I doing wrong?

ARTRAD:

LOAD ARTNR,

DOKNR,

`PRIS_ST_I`,

TBBEL;

SQL SELECT ARTNR,

DOKNR,

`PRIS_ST_I`,

TBBEL

FROM `C:\USERS\DOCUMENTS`\ARTRAD

WHERE DOKNR='1000';

Conditions in the script and filters

erichshiino — Tue, 31 May 2011 13:58:04 GMT

Are you reading a SQL Database or data files?

The SQL Select will only work for a database.

In other cases it be only a load statement.

Conditions in the script and filters

Tue, 31 May 2011 14:06:38 GMT

Good question.

I am reading from a ODBC-connection, which is pointing towards three DBF-files. Not sure if that answers the question.

So.... is there any other way of doing it? I find it so weird that it should be so difficult to limit the data?

Conditions in the script and filters

erichshiino — Tue, 31 May 2011 14:23:46 GMT

It`s really strange. In the mean time, you could load all the data and then keep only what you need.

It would be something like this:

tempSales:

Select * from SALES

noconcatenate

Sales:

Load * resident tempSales

Where Year(Sales_Date)>2009;

drop table tempSales;

QV 10 will probably not recognize the `noconcatenate` but it works.

Conditions in the script and filters

Tue, 31 May 2011 14:49:03 GMT

It worked!

Thank you!