How do I make a union selection?

[richnorris]

So, I have a requirement that users be able to select data to see where:

Origin = France

OR

Salesperson = France

So for example there is some data where

Origin = New York

Saleperson = France

and they would want to see these, and also the data where

Origin = France

Salesperson = New York

When I was asked this, I assumed this should be trivial, and simply some kind of advanced search term, but I can't seem to find any discussions on Qlik Community where this question gets answered?

I feel like I'm missing something incredibly obvious, but for the life of me can't see a way of doing it other than creating a number of flags in the ETL script. The only issue with this is that there will likely be a requirement going forward to have this dynamic, i.e. multiple Salespeople vs multiple Origins. We have a very large number of Origin and Salesperson entries, so creating a cartesian join of them to create flags would take some time, and naming them in a meaningful way might be difficult.

Sorry if I'm being dense, and thankyou for any input!

[jonathandienst · Accepted solution]

In set expressions a union operator is + between the two expressions - eg

     {<Origin = {'New York'}>+<Salesperson = {'France'}>}

(records where Origin is New York as well as records where Salesperson is France)

[richnorris]

Ok, great! Thanks for this, but one thing, I don't want this on a specific expression, I want to be able to apply it dashboard wide as a selection? Is it possible to use this set analysis in a search box, or would I still have to set up a flag to either apply or remove this from every expression in the dashboard?

I suppose I could probably create two input boxes, with a button that takes the values from them and applies it to the set analysis of some kind of universal expression (like Sum(1))? I'd rather not have to ask the users to input anything as obscure as that every time they want to do a union selection.

[dgreenberg]

Warning what I am about to propose is a dangerous precedent to set and should be given great thought before implimenting it.

It is dangerous because it goes agains the basic associative nature of Qlikview and users may want more and more of this.

If there are a finite number of combinations that you want you can accomplish this by creating one or more flags in the load script.

So for example in your load you can say something like:

Load *,

if (Origion ='France' or Saleseperson='France' ,1,0) as _FlagFrance

From table1.qvd (qvd);

So regardless of the specific Origion or specific Salesperson if either are set to france the flag will be set to 1.

Then you can filter on _FlagFrance and it will show anything with Origion = France or Salesperson=France.

The filter would be a listbox with _FlagFrance with a 1 or 0 showing.  You can change the label to be friendlier and can use 'True','False instead of 1,0 in the if statement.

Again this is ONLY useful if there is a finite number of combinations otherwise you will be making too many changes to the data model.

If there are more choices then the script could be more creative setting a _FlagCountry to a number and the another table matching that number to a CounryName.

Creating an inline table with

1, France

2, USA

3, Germany and so on.

If it's just 1 country you want to be able to search across Origion and Salesperson my script will work.

If it's several then what I describe next will work.

If it's infinite and will change this may not be a good idea.

Dan

[richnorris]

Thanks Daniel, that was what I was alluding to in my last paragraph, creating flags in the ETL, but as I say, the number of permutations is essentially a cartesian set of a large and growing list, so doing it that way is really a non starter, for all the reasons you mentioned.

I was hoping that was some kind of out of the box functionality for this, but I guess it really does come down to doing some weird kind of set analysis based on user input. Odd really, you'd think it would be quite a common request, but there we go! It's these quirks that keep me in a job!

[dgreenberg]

Richard,

I still think you can do it and not have to worry about the naming too much.

Google tells me there are less than 200 countries.

I would have to think about it for a while but I think we could create a script that creates a single flag called _Country that places the Country name in there when either Origion or Salesperson = that country.

Then your drop down could be labeled Country and have 1 value for each country.

I like this brain teaser, if I have spare time I might just see what I can come up with as a learning experience.

Dan

[richnorris]

Yeah, I know exactly what you mean, I'm not really supposed to be working on this right now but I keep coming back to it because its such an interesting conundrum!

The only thing I would say, is that I've not been entirely honest with my fields and have oversimplified for ease of explanation. In practice, the 'Countries' are not exactly limited to countries (as evidenced by 'New York') but rather refer to holdings. So, there are potentially infinite. (Although of course in practice there will probably be an identifiable number). The issue is that this method is simply not future proof. If it is decided that the users also want to be able to add another field, that ups the complexity by a large enough factor that it starts to become a major pain. This is a very likely situation.

If you can come up with a good way to do it that doesn't take an exponential amount of processing, I'm all ears!

[dgreenberg]

Education

Let's give up and explain due to the associative nature of Qlikview that's not the way it works.