topic Get Wildmatch() to search through only certain rows in App Development

Get Wildmatch() to search through only certain rows

wanderingcatto — Thu, 27 Oct 2022 07:42:20 GMT

Let's say I've a spreadsheet with hundreds of thousands of rows that look something like this:

Category	Items
Fruits	Apple, Banana, Cantaloupe, Durian
Companies	Apple, Samsung, Nokia, Blackberry
Fruits	Blackberry, Blueberry, Orange, Lemon

I've a table within my Qlik Sense dashboard that shows the "Items" column, but only where "Category" equals "Fruits". The number of rows where "Category" = "Fruits" is significantly smaller (~100) than the hundreds of thousands of rows I have in total.

I also have a input field within the dashboard that allows users to filter the "Items" column using a keyword. If there's no input, the "Items" column will not be shown. My expression for this "Items" column looks something like this currently:

=if(Wildmatch(Items, '*$(=GetFieldSelections([Input Field]))*'), Items, Null())

However, this expression seems to makes wildmatch() look through every single hundreds of thousands of rows despite the prior filter that I've already set in place. I suspect this because the wildmatch() search takes just as long (i.e. very long) regardless of whether or not there is any prior filtering done on the "Category" column.

How can I make wildmatch() search through just the 100 rows where "Category" = "Fruit", instead of every single row?

(P.S. I'm aware that Qlik Sense tables already have a search function. I'm over-simplifying my problem here. I really do need to use wildmatch() and have it search through only specific rows.)

Re: Get Wildmatch() to search through only certain rows

Fernando_Fabregas — Tue, 01 Nov 2022 14:28:04 GMT

Hi!

Is it possible to you to create a reduced child table only with Fruits records and splitting Items in single values ?

This will work much faster:

ChildTable:

LOAD Category,

SubField(Items, ',') as SingleItem

RESIDENT <YourTable>

where Category='Fruits';

In this way, perhaps you don't need to use an Input Box and WildMatch() to search, just a standar filter pane pointing to SingleItem field.

Regards, Fernando

Re: Get Wildmatch() to search through only certain rows

marcus_sommer — Wed, 02 Nov 2022 12:50:51 GMT

If your expression is really an expression you may use a set analysis for this task like:

only({< Items = {"*$(=GetFieldSelections([Input Field]))*"}>} Items)

- Marcus