topic Re: Multibox Expression for "Like" function in sql in QlikView

Multibox Expression for "Like" function in sql

Wed, 14 Sep 2011 08:17:58 GMT

In sql we can use Like follow by % to filter out records... i wonder how can i achieve it in qlikview multi box. as my record consist of

For example,

E12,E13,E14,A12,A13,C12,C13

i want to get only phase start with E in my multibox. thx

Re: Multibox Expression for "Like" function in sql

gandalfgray — Wed, 14 Sep 2011 08:22:34 GMT

Hi Jeffrey,

if you mean how to search in a multibox field, then you should enter: E*

to get the values starting with E.

(note that by default you get *E* when you start entering the searchstring, so you have to delete the leading *)

if your asking something else please clarify

hth/gg

Re: Multibox Expression for "Like" function in sql

rohit214 — Wed, 14 Sep 2011 08:27:14 GMT

just use{"E*"} in you expression

thanks

rohit

Multibox Expression for "Like" function in sql

Wed, 14 Sep 2011 08:32:41 GMT

well. the E12,E13,A14.... is actually result of column 'portnumber' in my table. now i wan to have one multi box to allow user to select only start with 'E' value from this column. in SQL server, we use "portnumber like 'E%'" so i wondering in qlikview, how do i acheive that.

Re: Multibox Expression for "Like" function in sql

gandalfgray — Wed, 14 Sep 2011 08:39:17 GMT

Hi Jeffrey

If you want the listbox/multibox only to show values starting with E

I suggest you add anoter field in your load script like this (assuming your original field is called Field):

Load ...,

If(Left(Field,1)='E', Field) As E_Field;

Select ...

then use E_field in the multibox

hth/gg

Re: Multibox Expression for "Like" function in sql

its_anandrjs — Wed, 14 Sep 2011 08:41:18 GMT

Hi,

See the attached sample file

Use some thing like this EX:

Temp:

load * inline

[

ColA

E12

E13

E14

A12

A13

C12

C13

];

load

Left ( ColA , 1 ) as New

Resident Temp;

Use new for multibox

See attached

Regards

Anand

Re: Multibox Expression for "Like" function in sql

its_anandrjs — Wed, 14 Sep 2011 08:56:40 GMT

Hi,

See the updated attached simply do it for others like A,C,E

HTH

Regards

Anand

Multibox Expression for "Like" function in sql

Wed, 14 Sep 2011 09:31:49 GMT

Hi, anand

So sorry that i am new to qlikview...may i know what is the syntax for me to write it wih my sql query as statement below? How to combine it with yours?thx

OLEDB CONNECT TO [....];

SQL SELECT ... ,'portnumber'

FROM a.dbo.Transactions
where [portnumber] like'K%' OR [portnumber] like'A%' OR [portnumber] like'E%';

????

Re: Multibox Expression for "Like" function in sql

gandalfgray — Wed, 14 Sep 2011 09:49:34 GMT

Hi jeffrey

Use the wizard to start creating the sql load statement and make sure the "preceeding load" checkbox is checked.

Then you will get a LOAD clause preceeding your SQL SELECT statement like this:

LOAD COL1,

COL2,

...

portnumber;

SQL SELECT ...

Then you can just modify the LOAD statement to add another field:

LOAD COL1,

COL2,

...

portnumber,

If(Left(portnumber,1)='E', portnumber) As Eportnumber;

SQL SELECT ...

hth/gg

Re: Multibox Expression for "Like" function in sql

its_anandrjs — Wed, 14 Sep 2011 09:55:38 GMT

Hi,

I suggest you to load a resident table of your sql table and add another column there like

SQl_Table:

OLEDB CONNECT TO [....];

SQL SELECT ... ,'portnumber'

FROM a.dbo.Transactions
where [portnumber] like'K%' OR [portnumber] like'A%' OR [portnumber] like'E%';

Res_Table:

Load

Left ( portnumber, 1 ) as New

Resident SQl_Table;

Add another column in the sql load like

Left ( portnumber, 1 ) as New

HTH

Regards

Anand

Multibox Expression for "Like" function in sql

Wed, 14 Sep 2011 14:30:07 GMT

Anand,

what is the different between your method with hth/gg's method? which one resolve less CPU resources in high performance? as my sql database initially 14,000,00 records? if every time reload i wondering how to speed it up... for every reload time

Thanks.

Multibox Expression for "Like" function in sql

its_anandrjs — Wed, 14 Sep 2011 14:42:54 GMT

Hi,

Difference is that when ever you use any resident tables it is faster than any other table loads because it is virtual and loaded fastly as compare to other and it is available in the application it self no need to take from any other. Thats why it is help ful and on this a calculated field are generated.

If you have any table just load data from there whether it is Query or Sql Table and create a resident load.

HTH

Best Rgds

Anand

Re: Multibox Expression for "Like" function in sql

gandalfgray — Wed, 14 Sep 2011 14:53:25 GMT

Hi anand

I would not agree on your statement about resident loads, (at least not in v9)

see this thread (which btw is an excellent thread to read for any QV developer to create better applications).

And besides my solution does a preceeding load directly on the sql select,

and not two loads from a sql source,

so after my solution there will only be one table created, but with anands solution there will be two (unless you later drop the first of course)

Anyway I think Jeffrey should try both solutions with his actual data, and see which performs best

regards

/gg

Re: Multibox Expression for "Like" function in sql

its_anandrjs — Wed, 14 Sep 2011 15:11:53 GMT

Hi,

Yes you right agreed but my concern is about any calculation related more i think i not mention there if you have any calculations or more complex expression on that the resident is more easy way. Yes the preceeding load directly on sql is correct. But if we need calculation on that basis is easy any way you got correct way of that.

Best Rgds

Anand