Find certain text in a string

[Anonymous]

Hi,

Looks like I need to go back and review the basic course. I have a listbox with certain results like

HeadWO

WOHead

HeadWOk

Some user has requested to put a new listbox to filter only results that contains "WO". Could someone please write the syntax for that? I was scratching my head with Match, MixMatch, and WildCard * function.

Thank you.

[Anonymous]

I would do this in the script, creating a new field using the original field;

Load

OriginalField,

if(wildmatch([OriginalField], '*WO*')=1, [OriginalField])as MyNewWOField

Resident

myTable;

[petter · Accepted solution]

In the application you can do it without adding a new field like this in a particular list box:

[Anonymous]

Gareth! That works too in a listbox expression:

if(wildmatch([OriginalField], '*WO*')=1, [OriginalField])

Thanks!

[Anonymous]

Petter! I was just confused having * at the first position if that will ignore the W. I was going to try it after a meeting, but glad to see it's working as needed on your example. Thanks so much!

[Anonymous]

No problem, I would be wary of using IF statements in Listboxes though, it may cause performance headaches further down the line.

Adding a new field in the script is best practice.

[petter]

I don't agree. Adding too many fields that has redundancy is a very bad practice. It is often done because developers are not sure what they want to use from the source tables. To compound the problem - many developers are used to disk-based databases where it is easy to have a lot of redundancy without seemingly running into problems. But believe me there are so many unoptimized slow databases out there due to this fact. When designing in-memory solutions like Qlik you will have to be much more aware of these potential pitfalls and strike the right balance between pre-calculation, memory-usage and speed.

Yes for a lot of indicators and flags it is helpful. With large amounts of data in-memory - redundancy between fields is killing speed and bloating memory.

[Anonymous]

I think its about finding a balance Peter.

I have done application optimisation for a number of companies and have seen up to 80% improvement in refresh rates simply by removing IF statements in Listboxes/Chart Titles/Calculated Dimensions etc by pushing them back to the script or into variables when they only need to be evaluated once =.

Unless pre-cached, these have to be calculated on every selection the user makes and can contribute to high CPU usage and a diminished experience for the user.

This seems to be a common gotcha, developers usually develop on a subset of data where it isn't an issue, it only becomes apparent when they push full production data sets into the document.

Bringing in 'too many fields' is symptomatic of not getting the requirements right before developing the application, ETL should be used to perform as much of the 'grunt' work as possible, the end document should always be cut down to the bare minimum allowed for the reporting required and scaled up as more requirements come to light.