topic Re: where clause in QlikView

where clause

Fri, 19 Oct 2012 14:27:40 GMT

Hi,

I'm trying to run a query with a where clause saying where "YEAR" = '2011' and '2012'; but i keep getting an error message. What is the correct way of doing this?

Thanks

Re: where clause

fdelacal — Fri, 19 Oct 2012 14:32:14 GMT

where YEAR = '2011' AND YEAR = '2012'

YOU CAN DO

WHERE YEAR> '2011' TOO..

REGARDS

Re: where clause

Fri, 19 Oct 2012 14:34:56 GMT

Thanks. What if i wanted to only pick up years, say, 2006 & 2012 but not the ones in between?

Re: where clause

MayilVahanan — Fri, 19 Oct 2012 14:37:49 GMT

where YEAR = '2006' AND YEAR = '2012'

use match(Year,'2006','2012') also

Hope it helps

Re: where clause

fdelacal — Fri, 19 Oct 2012 14:39:53 GMT

where YEAR = '2006' AND YEAR = '2012'
only load thesse yaer.

where YEAR > '2006' AND YEAR < '2012'

will be 2007,, 2008, 2009, 2010, 2011.

Hope it help..

you can add = too..

Re: where clause

kruppas78 — Fri, 19 Oct 2012 14:59:52 GMT

where YEAR = '2006' OR YEAR = '2012'

So you get both.

The Value in Field YEAR" can not be '2006" AND '2012' in one Row.

Re: where clause

Fri, 19 Oct 2012 15:07:34 GMT

Thanks but I'm still struggling to make this work. I have paste my script below. I used year in my original posting but its actually a filed called SSS_BATCH_ID i need. Both of the below return an error message

where "SSS_BATCH_ID" = '726' OR "SSS_BATCH_ID" = '829';

where "SSS_BATCH_ID" = '726' AND "SSS_BATCH_ID" = '829';

Re: where clause

MayilVahanan — Fri, 19 Oct 2012 15:10:33 GMT

Did you try match function?

Where match(SSS_BATCH_ID,726,829)

Re: where clause

kruppas78 — Fri, 19 Oct 2012 15:11:05 GMT

where SSS_BATCH_ID = '726' OR SSS_BATCH_ID = '829';

The quotes say QlikView that it is not a field.

Re: where clause

Fri, 19 Oct 2012 15:17:04 GMT

Yes i tried the match function and that didn't work either

Re: where clause

Fri, 19 Oct 2012 15:17:52 GMT

Thanks, but the quote marks work when i only ask to pick up say '726' or '829' on their own but not together

Re: where clause

MayilVahanan — Fri, 19 Oct 2012 15:22:35 GMT

Please check the attached file..

Re: where clause

Qrishna — Wed, 22 Apr 2015 18:40:21 GMT

Hi,

You may try creating a small temporary inline table with all required years.

TEMP_YEAR:

LOAD * INLINE [

Year

2014

2015

2016

];

FACT:

LOAD ID,

Fact_Date

FROM $(vQVDSourcePath)Facts.qvd (qvd);

Where Exists (Year,Year(Fact_Date));

DROP TABLE TEMP_YEAR;

This logic filters out all the unwanted years except the inline table's years.

and later dropping the temp table leaves with no extra table in the model.

Re: where clause

lawrenceiow — Wed, 22 Aug 2018 08:14:48 GMT

Might I suggest you try the File Wizard. When you come to the "File Wizard: Options" step you can click the "Where..." button and then make your selections. When you finish you can see how the script was written by the wizard. This will most likely show what went wrong when you tried it.

Most of the answers above are correct:

"SSS_BATCH_ID" = '726' OR "SSS_BATCH_ID" = '829';

Match("SSS_BATCH_ID",'726','829')

Both should work so you may find the error is actually something else which the wizard should make apparent to you.