topic Re: conditional where clause in QlikView

conditional where clause

Wed, 22 Feb 2012 10:48:48 GMT

Dear All ,

i want to implement a where clause which depends on other many variable

like example

SET var1='''

SET var2=''

and also var1 and var2 can contain multiple values

for using in

where vara=@var1

and varb=@var2

something like we use in procedures

Please giuve suggestion , if any

Re: conditional where clause

IAMDV — Wed, 22 Feb 2012 10:54:46 GMT

Hi,

Have you considered using the Match() in QlikView Scripting? This is an example of exists that is a part of Rob Wunderlich's cookbook available at his website robwunderlich.com.

The match function is a way to avoid writing

Load ...

From ...

Where Customer = 'A' or Customer ='B' or Customer='C'

and write it as

Load ...

From ...

Where match(Customer, 'A','B','C')

and it comes in a variety of flavors. WildMatch() is usually my favorite.

I hope this helps!

Cheers - DV

Re: conditional where clause

Wed, 22 Feb 2012 10:59:13 GMT

Hi I am using match

but instead of doing match (Customer,a,b,c)

can i do something like this

Set @abc="a,b'c"

where match(Customer,@abc)

With Regards

Pramod Kumar

Asst. Manager (IT)

Kent RO Systems Ltd

Re: conditional where clause

jagan — Wed, 22 Feb 2012 10:59:39 GMT

Try using the below expression

SET var1 = 'A', 'B', 'C';

SET var2 = 'A', 'B', 'C';

LOAD

vara,

varb,

FROM DATASOURCE

WHERE vara = Match(vara, $(var1))

AND varb = Match(varb, $(var2))

Hope this helps you.

Regards,

Jagan.

Re: conditional where clause

Wed, 22 Feb 2012 11:05:13 GMT

not working dear

With Regards

Pramod Kumar

Asst. Manager (IT)

Kent RO Systems Ltd

Re: conditional where clause

IAMDV — Wed, 22 Feb 2012 11:05:28 GMT

That's good! You can use the dollar sign expansion as mentioned by Jagan.

Dollar-sign expansions are definitions of text replacements used in the script or in expressions. The replacement of original text is made just before the script statement or the expression is evaluated.

Hope this helps!

Cheers - DV

conditional where clause

jagan — Wed, 22 Feb 2012 11:50:26 GMT

Hi,

Check the below script, it works

SET var1 = 'a','b','c';

SET var2 = 'a','b','c';

Temp:

LOAD * INLINE

[

vara, varb

a, b

d,d];

TEst:

LOAD * ,

1 AS Temp

RESIDENT Temp

WHERE Match(vara, $(var1)) AND

Match(varb, $(var2));

DROP table Temp;

Regards,

Jagan.

Re: conditional where clause

Wed, 22 Feb 2012 11:51:01 GMT

Thankx DV

your suggestion working fine after hit & trial

With Regards

Pramod Kumar

Asst. Manager (IT)

Kent RO Systems Ltd

Re: conditional where clause

Wed, 22 Feb 2012 11:52:05 GMT

Thanks Jagan

approach is working fine

With Regards

Pramod Kumar

Asst. Manager (IT)

Kent RO Systems Ltd

conditional where clause

jagan — Wed, 22 Feb 2012 11:58:57 GMT

Hi,

If your problems solves, close this discussion by selecting correct answer.

I helps others to find solution for this kind of problem.

Regards,

Jagan.