topic Re: Listbox search in more than one field in QlikView

Listbox search in more than one field

Anonymous — Fri, 13 Jan 2017 17:51:44 GMT

Hello,

Is there a way to create a Listbox that can search in more than one field?

I have 5 columns:

Col1, Col2, Col3, Col4, Col5

I'd like to create a Listbox that shows all the possible values of Col1...Col5 and can find any of those values in any fields

is it possible?

thanks

Re: Listbox search in more than one field

MK_QSL — Fri, 13 Jan 2017 18:14:05 GMT

Why can't you use Search object?

Re: Listbox search in more than one field

Anonymous — Fri, 13 Jan 2017 18:17:06 GMT

Checkbox selecting is a client requierement

Re: Listbox search in more than one field

MK_QSL — Fri, 13 Jan 2017 18:20:34 GMT

Create a list box using below expression..

=ValueList(

$(=

Concat(Distinct chr(39)&Col1&chr(39),',') & ',' &

Concat(Distinct chr(39)&Col2&chr(39),',') & ',' &

Concat(Distinct chr(39)&Col3&chr(39),',') & ',' &

Concat(Distinct chr(39)&Col4&chr(39),',') & ',' &

Concat(Distinct chr(39)&Col5&chr(39),',') & ',' &

)

Re: Listbox search in more than one field

MK_QSL — Fri, 13 Jan 2017 18:27:01 GMT

or use this..

QlikView App: Multi Column List Box

Re: Listbox search in more than one field

Anonymous — Fri, 13 Jan 2017 18:38:40 GMT

it displays all the possible values between Col1 and Col5, but when I click it, it just do nothing

What could I be doing wrong?

Thankss!!!!!

Re: Listbox search in more than one field

marcus_sommer — Fri, 13 Jan 2017 19:23:28 GMT

Beside the suggestion from Manish you could activate all listboxes (by using the shift-key and left-clicks an the caption or with a lasso-selection) and then just typing your search-value and it will search in all listboxes at the same time.

Another possibility would be to use the advanced search - if you activate a listbox and starts typing you will get a search-window and on the left will be your typed chars and on the right you will see a >> icon - if you clicked on them you get the advanced search which will work like the searchbox (and like within there you could limit within the search-settings from the listbox in which fields should be searched - it's most a good idea to limit these feature then otherwise the search will run against the whole datamodel which could be rather slow).

- Marcus

Re: Listbox search in more than one field

johnw — Fri, 13 Jan 2017 19:39:04 GMT

There's a small chance this will work for you as a list box expression.

=pick(match($Field,'Col1','Col2','Col3','Col4','Col5'),Col1,Col2,Col3,Col4,Col5)

It will list all distinct values of your fields in the list box. But in order to select a value, that value must only appear in one of the fields. Otherwise you can click on it, but nothing will happen. If it does appear in only one of the fields, it will select that value from that field, but also select all compatible values in the other four fields, and without showing them as selected in your list box. If only one of the columns is ever filled in at a time, then it only selects values in the field that has that value. But even then, the list box doesn't clear properly if you clear the underlying field rather than clearing the list box. A clear all still works.

So probably not very useful except in very specific cases.

Re: Listbox search in more than one field

MarcoWedel — Fri, 13 Jan 2017 23:16:58 GMT

Hi,

maybe one solution could be a combined field generated by a crosstable load:

table1:
LOAD RecNo() as ID,
    'Col1Value'&Ceil(Rand()*4) as Col1,
    'Col2Value'&Ceil(Rand()*4) as Col2,
    'Col3Value'&Ceil(Rand()*4) as Col3,
    'Col4Value'&Ceil(Rand()*4) as Col4,
    'Col5Value'&Ceil(Rand()*4) as Col5,
    Ceil(Rand()*100) as SomeFact
AutoGenerate 20;

tabCol:
CrossTable (Col, ColValue)
LOAD ID, Col1, Col2, Col3, Col4, Col5
Resident table1;

hope this helps

regards

Marco

Re: Listbox search in more than one field

Anonymous — Mon, 16 Jan 2017 18:24:14 GMT

Thanks!!!!!!!!!!! it was very useful, worked perfect!!!!!!

Re: Listbox search in more than one field

MarcoWedel — Mon, 16 Jan 2017 20:14:16 GMT

You're welcome

Glad you liked it.

Marco