topic Re: Multiple Selection Movie Genres in QlikView

Multiple Selection Movie Genres

Anonymous — Wed, 19 Apr 2017 16:03:35 GMT

Hi, I have a problem in QlikSense.

I have the following table

Movie Genres

M1 G1|G2|G3

M2 G1|G4

M3 G2|G3|G5

My purpose is that, an user can select multiple genres; considering as example:

- I select the genre G1

The result has all the movies having the genre G1 -> M1,M2,M3

- I select also the genre G3

The result has all the movies having the genres G1 and G3 -> M1,M3

I tried also SubField(Genres, '|') but nothing...

Any ideas?

Thanks!

Re: Multiple Selection Movie Genres

Anonymous — Wed, 19 Apr 2017 16:46:12 GMT

Why does your data have this strange structure?

I would transform it...

Re: Multiple Selection Movie Genres

Oleg_Troyansky — Wed, 19 Apr 2017 16:54:07 GMT

Hi Marco,

First of all, I do think that you need to "normalize" your data and extract genres one by one - you can use Subfield in your load script to do so.

Then, I'm not sure I understand your logic. If you require that both G1 and G3 are present for a movie, then only M1 should be selected (M3 doesn't have G1).

If you want movies with either G1 or G3 (the default QlikView behavior) then all three movies satisfy this condition.

Anyway, if you wanted to require that both G1 and G3 are present, then you can implement an exclusion logic that I describe in this blog post:

Q-Tip #15 – Excluding the Excluded | Natural Synergies

I'm teaching advanced Set Analysis techniques like this one, at the Masters Summit for Qlik - see if our agenda can fit your needs. You can also learn a hole lot of advanced Qlik stuff from my book QlikView Your Business.

Cheers,

Oleg Troyansky

Re: Multiple Selection Movie Genres

Anonymous — Wed, 19 Apr 2017 16:55:33 GMT

example:

LOAD Movie,
SubField(Genre,'|') as Genre;

LOAD * INLINE [
Movie, Genre
M1, G1|G2|G3
M2, G1|G4
M3, G2|G3|G5
];

Re: Multiple Selection Movie Genres

Anonymous — Wed, 19 Apr 2017 19:25:43 GMT

Hi Robin,

thanks for your answer! I also thought this kind of solution , applying this transformation.

However, the problem is the same because if I create a filter for the Genre field, considering another kind of dataset

Movie Genres

M1 G1|G2|G3

M2 G2|G4

M3 G1|G3|G5

M4 G3|G6

Transformed

Movie Genre

M1 G1

M1 G2

M1 G3

M2 G2

M2 G4

M3 G1

M3 G3

M3 G5

M4 G3

M4 G6

If I am interested in finding all the movies that have only G1 and G3, i can't applying the filter because the output would be {M1,M3,M4} insteaf of {M1,M3} which is my goal.

Any suggestion?

Thanks again

Re: Multiple Selection Movie Genres

Anonymous — Wed, 19 Apr 2017 19:29:33 GMT

Hi Oleg,

thanks for your answer about the first suggestion, my bad, I wrote bad the third movie, the original is

Movie Genres

M1 G1|G2|G3

M2 G1|G4

M3 G1|G3|G5

I read the link you send, so for this kind of dataset and fields, which would be the right syntax to apply?

Thank again

Re: Multiple Selection Movie Genres

Oleg_Troyansky — Wed, 19 Apr 2017 20:15:49 GMT

Hi again,

have you had a chance to read my blog article, that I pointed out above? It solves EXACTLY the same problem. You can just copy my solution as is and be done with it.

cheers,

Oleg Troyansky

Re: Multiple Selection Movie Genres

Anonymous — Thu, 20 Apr 2017 08:30:00 GMT

Hi Oleg,

I read your article but I still can't manage to achieve my goal;

This is my dashboard:

I'd like to select multiple Genres in the selected filter, in order to obtain (in the table on the right) all the movies having the selected Genres.

Basing on the syntax proposed on your article, I tried

only({<Movie=E({<Genre=E(Genre)>})>} Movie)

and i put this syntax in several objects; however, after filtering multiple genres, the result is not still good...

Re: Multiple Selection Movie Genres

Anonymous — Thu, 20 Apr 2017 16:31:56 GMT

I tried another approach...

you'll need always the dimension movie, so aggr() will be needed if there's no movie dimension...

Re: Multiple Selection Movie Genres

Oleg_Troyansky — Thu, 20 Apr 2017 21:04:49 GMT

Hi Marco,

apologies, now I remember that the question that I answered in my blog was slightly different - the question was how to select ONLY those that have the selected two genres, but not any other genres. Your issue is slightly different... I need to think a bit longer about your problem...

cheers,

Oleg Troyansky

Re: Multiple Selection Movie Genres

Oleg_Troyansky — Thu, 20 Apr 2017 21:19:31 GMT

Found this in the forum, I think it's quite helpful:

And-Mode: How to enable AND and NOT selections with Green, White, Grey...and Red!