Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
marco_giannuzzi
New Contributor II

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
roharoha
Valued Contributor III

Re: Multiple Selection Movie Genres

Why does your data have this strange structure?

I would transform it...

Re: Multiple Selection Movie Genres

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

roharoha
Valued Contributor III

Re: Multiple Selection Movie Genres

example:

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

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

marco_giannuzzi
New Contributor II

Re: Multiple Selection Movie Genres

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

marco_giannuzzi
New Contributor II

Re: Multiple Selection Movie Genres

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

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

marco_giannuzzi
New Contributor II

Re: Multiple Selection Movie Genres

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

roharoha
Valued Contributor III

Re: Multiple Selection Movie Genres

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

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

Community Browser