Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Multibox Expression for "Like" function in sql

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

1 Solution

Accepted Solutions
gandalfgray
Valued Contributor

Re: Multibox Expression for "Like" function in sql

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

13 Replies
gandalfgray
Valued Contributor

Re: Multibox Expression for "Like" function in sql

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
Contributor III

Re: Multibox Expression for "Like" function in sql

hi

just use{"E*"} in you expression

thanks

rohit

Not applicable

Multibox Expression for "Like" function in sql

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
Valued Contributor

Re: Multibox Expression for "Like" function in sql

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

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

Hi,

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

HTH

Regards

Anand

Not applicable

Multibox Expression for "Like" function in sql

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
Valued Contributor

Re: Multibox Expression for "Like" function in sql

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

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


Community Browser