topic Re: Using a listbox to search through all fields? in QlikView

Using a listbox to search through all fields?

Wed, 25 Nov 2020 16:16:04 GMT

Hey,

Assuming I have multiple fields that contain names and those names can be found on any of the fields. How can I create a listbox where when I select a name it will search through all the fields and return all the rows that contain that name regardless of the field?

Example I have a banker field, executing banker, analyst and in all three fields a name say Jim Harbough can appear in any of those three fields. I want to be able to select all rows where the name Jim Harbough appears.

Thanks

Re: Using a listbox to search through all fields?

swuehl — Mon, 30 May 2016 23:51:54 GMT

You can create a link table with the RecordID and all name fields concatenated into one.

If you then use this field for filtering, it will return all records where the name occurs in any of the fields.

CROSSTABLE (NameField, NameValue)

LOAD RecID, BankerField, ExecutingBanker, Analyst

RESIDENT YourTable;

Then use NameValue field to filter your records.

Re: Using a listbox to search through all fields?

swuehl — Mon, 30 May 2016 23:55:44 GMT

Depending on your requirements, another option might be to just use the associative search in a list box:

Text searches

Re: Using a listbox to search through all fields?

Tue, 31 May 2016 00:14:21 GMT

Ideally I'd like for the user to select their name and no matter what field they appear in they can see all Deals they are associated with. I think the associative search would allow them to select one role at a time.

For your first option are you saying I should have NameValue be something like CONCATE(BankerField,Executing,Analyst) ?

so that for example

DEAL ID Banker Executing Analyst

10000 John Jim Jill

would become

[10000,'John,Jim,Jill'] and then search the namevalue?

What object would be cleanest to use as the filter?

Re: Using a listbox to search through all fields?

PrashantSangle — Tue, 31 May 2016 05:32:42 GMT

Hi,

Not sure what you looking for,

but you can use Search Object instead of creating list box

Search Object have capacity to search through all fields or if you want to limit your search for fields then you can also achieve it through search Object.

Explore its property for more details.

Search Object ‒ QlikView

Regards

Re: Using a listbox to search through all fields?

Tue, 31 May 2016 06:46:45 GMT

A search object would be helpful in above scenario.

associative search is the feature .

like attached screenshot.

Re: Using a listbox to search through all fields?

Tue, 31 May 2016 07:13:53 GMT

Use a search box object and in the properties> general tab you can specify whether you want to search all fields, a list of fields or particular group of fields. Also there is an advanced option called Default Search Mode in the General tab if you want further customized search so look into those if needed and lastly also check out the Presentation tab of the same, in that you can untick the

Include Excluded Checkbox to just show the matching results and Collapse when results exceed option.

Regards ,

Salman.

Re: Using a listbox to search through all fields?

swuehl — Tue, 31 May 2016 12:01:19 GMT

No, I was suggesting to use the CROSSTABLE LOAD prefix to create a new, transformed table with a single field that contains all name values:

The Crosstable Load

Re: Using a listbox to search through all fields?

Fri, 10 Jun 2016 16:29:49 GMT

Thank you your trick worked!