conditional where clause

[#1]

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

[IAMDV]

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

[#3]

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

[jagan]

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.

[#5]

not working dear

With Regards

Pramod Kumar

Asst. Manager (IT)

Kent RO Systems Ltd

[IAMDV]

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

[jagan]

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.

[#8]

Thankx DV

your suggestion working fine after hit & trial

With Regards

Pramod Kumar

Asst. Manager (IT)

Kent RO Systems Ltd

[#9]

Thanks Jagan

approach is working fine

With Regards

Pramod Kumar

Asst. Manager (IT)

Kent RO Systems Ltd

[jagan · Accepted solution]

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.