Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Multiple Selection Movie Genres

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!

10 Replies
Anonymous
Not applicable
Author

Why does your data have this strange structure?

I would transform it...

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Anonymous
Not applicable
Author

example:

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

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Anonymous
Not applicable
Author

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...

Anonymous
Not applicable
Author

I tried another approach...

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

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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