Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Conditions in the script and filters

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.

1 Solution

Accepted Solutions
erichshiino
Partner - Master
Partner - Master

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.

View solution in original post

6 Replies
erichshiino
Partner - Master
Partner - Master

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

Not applicable
Author

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

erichshiino
Partner - Master
Partner - Master

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. 

Not applicable
Author

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?

erichshiino
Partner - Master
Partner - Master

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.

Not applicable
Author

It worked!


Thank you!