Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Why does your data have this strange structure?
I would transform it...
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
example:
LOAD Movie,
SubField(Genre,'|') as Genre;
LOAD * INLINE [
Movie, Genre
M1, G1|G2|G3
M2, G1|G4
M3, G2|G3|G5
];
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
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
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
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...
I tried another approach...
you'll need always the dimension movie, so aggr() will be needed if there's no movie dimension...
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