topic Straight Table Fuzzy Search in QlikView

Straight Table Fuzzy Search

Sat, 05 Oct 2013 15:34:58 GMT

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

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!

Re: Straight Table Fuzzy Search

swuehl — Sat, 05 Oct 2013 16:14:40 GMT

Maybe like this:

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.

Re: Straight Table Fuzzy Search

Sat, 05 Oct 2013 16:27:42 GMT

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

Re: Straight Table Fuzzy Search

swuehl — Sat, 05 Oct 2013 17:36:30 GMT

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?

Re: Straight Table Fuzzy Search

Sat, 05 Oct 2013 18:53:30 GMT

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.

Re: Straight Table Fuzzy Search

Brice-SACCUCCI — Mon, 07 Oct 2013 08:23:27 GMT

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

Re: Straight Table Fuzzy Search

Mon, 07 Oct 2013 12:57:42 GMT

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.

Re: Straight Table Fuzzy Search

Brice-SACCUCCI — Mon, 07 Oct 2013 13:06:50 GMT

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.

Re: Straight Table Fuzzy Search

Mon, 07 Oct 2013 13:40:00 GMT

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...

Re: Re: Straight Table Fuzzy Search

swuehl — Mon, 07 Oct 2013 13:47:00 GMT

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

Re: Re: Straight Table Fuzzy Search

Mon, 07 Oct 2013 13:52:41 GMT

How would I call out a field "Handler" in a table "SlicerDicer" as the replacement for the

argument in the above script?

Re: Straight Table Fuzzy Search

swuehl — Mon, 07 Oct 2013 14:00:46 GMT

Not sure if I understand your request, I would just replace field name Table with field name Handler.

Are you still using two data island tables?

Re: Re: Straight Table Fuzzy Search

Mon, 07 Oct 2013 14:15:55 GMT

I have attached a slimmed down version of exactly what I am trying to accomplish. The Table SlicerDicer should return all rows in which the field Handler in the table contains any of the values selected in the HANDLERID ListBox.

Re: Re: Re: Straight Table Fuzzy Search

swuehl — Mon, 07 Oct 2013 14:29:24 GMT

Having both tables linked by ID makes the thing more complicated, is this really needed?

If so, you can try like attached (using an alternate state for the HandlerID selection, and using conditional expressions in expression tab instead conditional hides in presentation tab).

Re: Re: Straight Table Fuzzy Search

Mon, 07 Oct 2013 14:38:31 GMT

This attached example is not working as I need it to. The example as I gave it is set up exactly how it needs to be. This is actually a much simplified example from what I really need. In your attached example, I am still not getting the correct rows returned.

Re: Re: Re: Straight Table Fuzzy Search

swuehl — Mon, 07 Oct 2013 14:46:29 GMT

You are correct, I mixed up the fields in the search expression. Check this modified version.

Re: Re: Straight Table Fuzzy Search

Mon, 07 Oct 2013 15:06:40 GMT

This is a HUGE help!!! It's almost there - thank you so much! The only thing is that if multiple values are selected, this is not working correctly. It's returning rows that don't include any of the values selected.

Re: Re: Re: Straight Table Fuzzy Search

swuehl — Mon, 07 Oct 2013 15:16:33 GMT

Yes, you need to create multiple search terms with wildcards for that, maybe like attached.

Re: Re: Straight Table Fuzzy Search

Mon, 07 Oct 2013 15:20:25 GMT

You are a lifesaver!!! Thank you SO much for this!

Re: Re: Straight Table Fuzzy Search

Mon, 07 Oct 2013 15:38:24 GMT

Why is it that when I add in additional fields for display, then all rows are returned instead of only those which match the Handler selected?