Straight Table Fuzzy Search

[#1]

Hello,

I'm trying to do a fuzzy search of a straight table.  I have a list box with possible values for a given field and I want to return any rows in the table which contain the selected values (the field can have concatenated values).  For example:

List Box for Field X:

Abc

Ad

Bc

Values in Table Field X:

Abc;Ad

Abc

Bc;Ad

Therefore, if the user selects Ad from the List Box, rows 1 and 3 should be returned.  If the user selects Abc & Ad from the List Box, rows 1, 2 and 3 should be returned.

I hope this makes sense.  I can't figure out how to do this and I've found nothing that fits my needs in all of my searching.  Thanks in advance for your help!

[swuehl]

Maybe like this:

X:

LOAD Recno() as Row,

          subfield(FieldX,';') as FieldX

INLINE [

FieldX

Abc;Ad

Abc

Bc;Ad

];

You can select your search strings directly in FieldX, Row gives you the expected outcome then.

[#3]

Is there no logic I can put on the table itself so that nothing has to be loaded inline? My listbox is built off of a table field and the straight table contains the values from the listbox but they may be concatenated in the chart. I just want the chart to do a contains on the selected value instead of an exact match.

Sent from my mobile device

[swuehl]

Not sure if I fully understand, if your straight table expression is based on the field of your list box, just concatenated, selecting the values in your list box should filter your table accordingly, shouldn't it?

Could you upload a small sample app?

[#5]

No, it doesn't. If in my list box I choose value 'a', my table should return rows like 'a', 'a; b'. If in my list box I choose values 'a' and 'b', my table should return rows like 'a', 'a; b', 'b', 'b; c'. I can upload an app at a later time as I'm not currently at my computer.

[Brice-SACCUCCI]

Hi,

you could do this by creating a "search table" associating criteria and values in the script.

But this could be time and mermory expensive if you have a lot of data...

See the attached example.

Regards,

Brice

[#7]

I don't want to have to script all of the possible search results as they are constantly changing and there is quite a bit of data.  I just want the table to return all "like" values instead of exact matches of the selected listbox value.  I'm a bit surprised this isn't a standard functionality.

[Brice-SACCUCCI]

Actually in your example, I didn't notice the data consisted in concatenated values.

You just have to create one table:

SubData:

LOAD

Data,

SubField(Data, ';') as SubData

RESIDENT

Data;

The associative engine of QlikView associates all values which are the same. This is computed at the end of the execution of the script and allows for very fast response times and easy navigation across the data.

You can do fuzzy search by typing "Abc*" in the field search box or in a search object, but you cannot LINK 2 fields automagically (= with the green, white, grey color-coding) when values are almost the same or contained one in each other.

BUT, as I'm suggesting, it is very easy to build those associations in your case, just by adding a field to the model.

This new field will have as many DISTINCT values as the "sub-data" you have. By "sub-data", I'm talking about  DISTINCT values are what matters in QlikView as 'Abc' is just stored once, even if there are 1 000 000 instances of this value.

Hope this helps!

Regards,

Brice

EDIT:

As an alternative, you could use an Expression in your list box but this would probably create performance issues. It's a choice between memory usage/load time OR navigation lag.

Just create a listbox with this expression instead of the field: SubField(Data, ";").

This way, no need to create any data. I'm attaching a new sample.

[#9]

I think I understand what you're saying, but I can't predict the concatenated values.  The concatenated values are in a field in my database.  My listbox values are coming from the possible individual values which are from a different table in my database.  I've attached an example which contains the selection ListBox (ListBox) and the selected ListBox (Table). - of course this doesn't work the way I need.  I need the Table ListBox to actually be a Table and the values are just one of the many fields in the table.  Even as this is now, only exact matches are being returned (despite my wildmatch expression being used).  Attachment to follow as I can't figure out how to do it right now...

[swuehl]

Maybe like attached, just added wildcards to your wildmatch() function.