topic Advanced Search - 2 Fields - OR Logic in QlikView

Advanced Search - 2 Fields - OR Logic

haymarketpaul — Fri, 18 May 2012 15:17:35 GMT

Hi There

QV11 SR1 64-bit

I have 2 fields::

Field1 JobTitle in Listbox

Field2 JobFunction Dimension within Straight Table Chart

My users would like to be able to search and select within both of these at the same time using OR logic, so the following sort of thing...

=[JobTitle ] like '*Manager*

[JobTitle ] like '*Director*'

[JobFunction] like '*Executive*'

[JobFunction] like '*Management*'

The straight table chart does not itself have an advanced search option.

So if i right-click the JobTitle listbox and choose Advanced Search with the Search in drop-down set to JobTitle, using the statement above i get some results which at first glance appear correct (3223 record count)

However, if i change the Search in drop-down to JobFunction and try the same thing i get different results (2565 record count)

Does anyone have an idea why that might be?

Is there another way for the user to do this kind of thing e.g. a set analysis expression?

Advanced Search - 2 Fields - OR Logic

Jason_Michaelides — Fri, 18 May 2012 16:01:02 GMT

Hello mate,

This should work:

1. Create a variable vSearchString (empty)

2. Add an Input Box object tied to that variable (this is for your users to use and they should type as many comma-separated values as they want)

3. Create a second variable vWildMatch:

=Chr(39) & Chr(42) & Replace(vSearchString,',',Chr(42) & Chr(39) & ',' & Chr(39) & Chr(42)) & Chr(42) & Chr(39)

this builds a WildMatch()-ready string with wildcards etc.

4. Then, to list all the registrants that have either a JobTitle or a JobFunction that matches the search, put the following in a text box:

=Concat(DISTINCT IF(WildMatch(Jobtitle,$(vWildMatch))OR WildMatch(JobFunction,$(vWildMatch)),RegistrantName),',')

or, to count the registrants:

=Sum(DISTINCT IF(WildMatch(Jobtitle,$(vWildMatch))OR WildMatch(JobFunction,$(vWildMatch)),Counter_User))

Hope that helps!

Jason

(Now how did I know you were counting registrants? And how did I know you have a counter flag!!??)

Advanced Search - 2 Fields - OR Logic

haymarketpaul — Fri, 18 May 2012 16:16:19 GMT

Thanks mate

I'll look into this on Monday.

You're not too busy over there then

Advanced Search - 2 Fields - OR Logic

Jason_Michaelides — Fri, 18 May 2012 16:31:43 GMT

Never too busy for HMG

Advanced Search - 2 Fields - OR Logic

haymarketpaul — Mon, 21 May 2012 16:19:54 GMT

Finally got around to looking at this again

Had to add in =1 in a couple of places to get this to work...

=Concat(DISTINCT IF(WildMatch(Jobtitle,$(vWildMatch)=1)

OR WildMatch(JobFunction,$(vWildMatch)=1),RegistrantName),',')

The count example doesn't seem to work at all even with =1's added?

Not sure this would help me anyway as ideally i want to actually select the results so the users can output the records to excel or make further selections.

If the OR logic worked in the advanced search box it would be great...it doesn't though, in fact it's rather misleading from what i can see.

I've trawled every other post on this subject and there does not as yet appear to be a nice easy way to achieve this. A macro has been suggested by several people but looking to avoid those if possible.

I'm thinking i may as well concatenate the job title & job function together in a listbox for the sake of allowing someone to search on the 2 together and be able to select them.

Thanks for trying anyway.

Advanced Search - 2 Fields - OR Logic

Jason_Michaelides — Mon, 21 May 2012 16:31:13 GMT

Email me the app mate and I'll have a look. The above should work fine and so should the advanced search.

Advanced Search - 2 Fields - OR Logic

Jason_Michaelides — Tue, 22 May 2012 18:17:55 GMT

Right...got the email, cheers. First things first - having re-read your issue above, the reason for the difference in counts using the advanced search is pretty clear. You should not be performing this search in the JobTitle field as not every user has one. Instead, use the advanced search on the UserID field as there is a unique value for every record. So:

=WildMatch([Job Function BR],'*manager*','*management*','*director*','*executive*') OR WildMatch([Job Title BR],'*manager*','*management*','*director*','*executive*')

in the UserID field will give the correct result of 5,952 (on BR tab). It searches for every UserID associated with a Job Title or Job Function returned by the WildMatch() function.

Now...I've had some fun with this today and ended up with something I think you'll like! I've created 4 new variables:

vSearchString:

vWildMatch:

=Chr(39) & Chr(42) & Replace(Replace(vSearchString,', ',','),',',Chr(42) & Chr(39) & ',' & Chr(39) & Chr(42)) & Chr(42) & Chr(39)

This builds a WildMatch expression based on the comma-separated string in vSearchString (which is set by your users with a input-box). It replaces all comma-and-spaces combos with just a comma in case your users put a space between the strings to search for.

vActiveBrandTitle and vActiveBrandFunction

'[Job Title ' & $(vActiveBrand) & ']'

'[Job Function ' & $(vActiveBrand) & ']'

These are so you can copy and paste-link the new search objects onto any brand's page and it'll search the correct fields.

vWildMatchSearchResult:

='Sum({<[HBM Brand]= {'& $(vActiveBrand) &'}, UserID={"=WildMatch([Job Title ' & $(vActiveBrand) & '],' & vWildMatch & ') OR WildMatch([Job Function ' & $(vActiveBrand) & '],' & vWildMatch & ')"}>} Counter_FACT)'

This builds a set analysis expression to count the number of users within the brand that have either a Job title or Job Function containing any one of the search strings. Neat!

The Apply button runs an advanced search on UserID for the results of the set analysis. You can't directly run advanced searches in button actions but you can cheat and dynamically build the expression:

=IF(vSearchString='','','=WildMatch([Job Title ' & $(vActiveBrand) & '],' & vWildMatch & ') OR WildMatch([Job Function ' & $(vActiveBrand) & '],' & vWildMatch & ')')

I've also added a Clear button to delete the search strings and reset UserID.

Have fun mate...I'll email the app back to you.

Jason

Advanced Search - 2 Fields - OR Logic

haymarketpaul — Wed, 23 May 2012 08:35:42 GMT

wow thanks mate, you've been busy.

i'll take a look at all that today.

The app you emailed back didn't make it through as it's over 10MB. Maybe you could email it to my hotmail: pnockolds@hotmail.com

thanks again

Advanced Search - 2 Fields - OR Logic

haymarketpaul — Wed, 23 May 2012 15:24:04 GMT

Yep that's great mate. Works nicely.

Thank for that.

As you say the advanced search works nicely on UserID. I'll show them how to do that as well in case they only want to search for some of the words in specific fields.

I'll see what i can conjure up for you next time keep you on your toes.

Advanced Search - 2 Fields - OR Logic

Jason_Michaelides — Wed, 23 May 2012 15:28:27 GMT

Glad it works. Hope everyone at HMG is good.

(I've just read back over my answer - God I'm a geek!)

Re: Advanced Search - 2 Fields - OR Logic

Wed, 03 Sep 2014 14:44:29 GMT

Is the advanced search you created case sensitive?