Multibox Expression for "Like" function in sql

[#1]

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

[gandalfgray]

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

[rohit214]

hi

just use{"E*"} in you expression

thanks

rohit

[#4]

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.

[gandalfgray]

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

[its_anandrjs]

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

[its_anandrjs]

Hi,

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

HTH

Regards

Anand

[#8]

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%';

????

[gandalfgray · Accepted solution]

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

[its_anandrjs]

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;

OR

Add another  column in the sql load like

Left ( portnumber, 1 ) as New

HTH

Regards

Anand