Pattern Matching in Search Field/Object

[greenee]

Hello All,

Have a request from user, if there is pattern Matching in searching field or object ?

Something like this, which would give him most of what he wants,  
???? (P),*  (1st 4 chrs of field followed by space, then (P), followed by anything.

However, he wants 1st character of the Dimension value is a letter, (or maybe he want to search for 1st 2 chrs as letters) followed by next  x number of chrs as numeric followed by the rest of what he wants. 
Something like (?chr)(?#)(?#)(?#) (P), *  

Is there a way of doing this in QlikView, not Sense. 
I don't think there is.  Please prove me wrong 😉

Many thanks
Regards
Rob

[Or · Accepted solution]

If your user is advanced enough, this can be done, e.g. first two numbers, next two uppercase letters, then a space:

=IsNumber(Left(Field,2)) and Len(PurgeChar(Mid(Field,3,2),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'))=0 AND mid(Field,5,1) = ' '

However, there's no option to search using a regular expression or something of that nature that I know of.

[greenee]

Thanks for your prompt response Or.  Unfortunately it doesn't work, and IsNumber is coming up as Function unknown, so I have changed to IsNum.   (QV2017 SR7, v12.20.20800)

That's what I thought, you can't do this type of search in the search box or drop down select (unless I have misunderstood), I have tried with Advanced search Ctrl Shft F, (not sure it's working in Chrome for me TBH though).

example of pattern user wants to find .   AA1234 (P)* or A123 (P)* or ABC12 (P)* etc.

But although your expression seems to be the wrong way round, (nmbrs then chrs) you got me thinking. 

The more advanced users could create a Personal Chart (Straight Table in the app)  with the Field as Dim and Expressions as the 'Finder' of the criteria needed. (Is that what you meant ?)  . This way they could have many different combinations of Flags and select accordingly.   

I'll work on this a bit more and post back my working solution expressions.

Appreciated,
Rob.

[Or]

What I whipped up was typed into the box without syntax checking and meant as an example. Your user would be writing their own formula based on the specific combination of characters / numbers / whatnot they want to test for. All of these can be typed into standard Qlik search (assuming the expression is valid), and you can also use Match() or WildMatch() where that might fit, e.g. Wildmatch(String,'???? (P)*') AND IsNum(Left(String,2)). It just depends on your user knowing which functions to use to achieve the desired search.

[greenee · Accepted solution]

Thanks for the clarification and help on this Or.

I have done this which seems to fit the bill for the user. They can select whichever suits their need and adjust as necessary. 

Example =  Field values...
B303 (P), United Kingdom           (= Selected query 1)
B303 XYZ ABC 123 (P), United Kingdom   (=NOT Selected query 1)

BV30456 (P), United Kingdom          (= Selected query 2)

BCP303456 (P), United Kingdom    (= Selected query 3)

Query 1
To find 1 chr followed by numbers before '(P)' in Dimension Value
=( not IsNum(Left([FIELD],1)) AND IsNum(Mid([FIELD],2,FindOneOf([FIELD],'(P),')-2)) )

Query 2
To find 2 chrs followed by numbers before '(P)' in Dimension Value
=( (not IsNum(Left([FIELD],1)) AND not IsNum(Mid([FIELD],2,1))) AND IsNum(Mid([FIELD],3,FindOneOf([FIELD],'(P),')-2)) )

Query 3
To find 3 chrs followed by numbers before '(P)' in Dimension Value
=( (not IsNum(Left([FIELD],1)) AND not IsNum(Mid([FIELD],2,1)) AND not IsNum(Mid([FIELD],3,1))) AND IsNum(Mid([FIELD],4,FindOneOf([FIELD],'(P),')-2)) )

Etc. etc.

In the field you want to search do this (do not do it a main search object with multiple fields, as performance is affected and I couldn't get it to work in that type of object)

1. Hit the drop down arrow
2. Press space bar to bring up criteria search box
3. Remove the 1^st * and then paste in the search formula you want to find and REMOVE the last * after you have copied the formula.
4. Hit return.  

If any values fit your criteria then they will be selected.

Obviously, change the field name for the one you want to search in. and change the (P),  if you want to find something else in the dimension string value.

Once the search has found what you want it will show the formula in the My Selection box.

Regards
Rob

[Or]

You don't need to muck around with the ** aspect if you set the search mode to Normal under settings, for what that's worth. You can also just start typing once you've hit the search icon, no need to hit space bar, I believe.

[greenee]

Thanks, but the app is in Prod, so can't change search settings for the moment.