topic Re: where clause in QlikView

where clause

Tue, 28 Oct 2014 13:43:46 GMT

Hi all,

I need to know how to use WHERE clause in expression.

thanks in advance

Re: where clause

jpenuliar — Tue, 28 Oct 2014 13:45:29 GMT

try SET ANALYSIS

Set Analysis Wizard for QlikView | qlikblog.at

Re: where clause

Tue, 28 Oct 2014 13:47:29 GMT

Hi,

Sorry this will be use in backend(script)

Use Match instead of where clause.

suppose country is ind,aus,usa we need then we use match(country,'ind','aus','usa')

hope this helps

using set analysis instead of where clause

this will use in expression

sum({<country={'ind','aus','usa'}>} Sales)

Re: where clause

Tue, 28 Oct 2014 13:47:40 GMT

Hi,

you can use Set analysis to use where clause in expressions.

For eg: Sales where Country = India.

Sum({<Country={'India'}>}Sales)

Re: where clause

mdmukramali — Tue, 28 Oct 2014 13:49:32 GMT

Dear Deepak,

you can use set analysis for Where clause in expression

Re: where clause

upaliwije — Tue, 28 Oct 2014 13:57:57 GMT

Can you please explain what your exact requirement is

Re: where clause

Peter_Cammaert — Tue, 28 Oct 2014 13:59:30 GMT

A "WHERE clause" is scripting terminology, to be used in SQL SELECT and LOAD script statements only.

Expressions in UI objects like charts and text boxes can limit the range of data they operate/aggregate on by using one or more of the following techniques:

Use dimensions to separate groups of values. The expression will operate only on the field values associated with that particular dimension value
Use IF () functions to explicitly select the values you want the expression to operate on. An IF () function call can be embedded anywhere a value, a field or a parameter is expected. The evaluation of IF functions can be costly when aggregating a large data set.
Use set analysis to limit the scope of an expression before the aggregation is performed. Very powerful, but can be complex and difficult to master. Punctuation is king.

QV Desktop Help is your friend for more details about set analysis.

Peter

Re: where clause

jpenuliar — Tue, 28 Oct 2014 14:00:17 GMT

agree

Re: where clause

its_anandrjs — Tue, 28 Oct 2014 14:04:40 GMT

Hi,

Where clause is not used in the expression it is load script functions and use there you can use it in load script

Ex:-

Load * inline

[

City

Berlin

London

Rio

Spain

] Where Match(City,'Berlin','London');

And

In Expression you can use it with SET expression or with any If conditions

With SET

Count( {<City = {'Berlin','London'}>} City )

With IF

Count( If( Match(City,'Berlin','London'), City )

Regards

Anand

Re: where clause

Kushal_Chawda — Tue, 28 Oct 2014 17:34:50 GMT

Go for set analysis

Sum({<TYPE ={'A','B'}, ID ={'1','2'}>} Sales)

Means Provide Sum of sales where TYPE = A,B and ID=1,2