Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a beginner question that's probably been asked and answered a thousand times already. If someone has a link to examples, I would be grateful. As a non-SQL user, I lack the vocabulary to elucidate my question well.
My simplified example table
Owner,Sequence,Pet
Alice,1,cat
Bill,1,dog
Charles,1,fish
Charles,2,hamster
Charles,3,cat
In the dashboard, a user selects "cat" to see all the cat owners (Alice and Charles).
I want to display all the fish owners and all their pets.
What I'm getting:
Owner | Sequence 1 | Sequence 2 | Sequence 3 |
---|---|---|---|
Alice | cat | -- | -- |
Charles | -- | -- | cat |
* from this, I know I have 2 cat owners and they are Alice and Bill, but "fish" and "hamster" are NOT selected, so they are not displayed.
What I want:
Owner | Sequence 1 | Sequence 2 | Sequence 3 |
---|---|---|---|
Alice | cat | -- | -- |
Charles | fish | hamster | cat |
I suspect I need to transform my data during load to make it work as planned. Create a field called "cat owner(Y/N)" for every record, but there are lots of different pets and this doesn't seem like a good practice. I didn't post my original code because it's confusing, involving both categories and sequences for each "pet"
thanks.
Dimension = Owner
Sequence1 = Concat({1<Sequence={'1'},Owner=P(Owner)>}Pet)
Sequence2 = Concat({1<Sequence={'2},Owner=P(Owner)>}Pet)
Sequence3 = Concat({1<Sequence={'3'},Owner=P(Owner)>}Pet)
Dimension = Owner
Sequence1 = Concat({1<Sequence={'1'},Owner=P(Owner)>}Pet)
Sequence2 = Concat({1<Sequence={'2},Owner=P(Owner)>}Pet)
Sequence3 = Concat({1<Sequence={'3'},Owner=P(Owner)>}Pet)
Works perfectly. Thanks so much!
My only hiccup was using sequences as dimensions rather than measures. --gotta use measures.
for other noobs like me:
Here's a link to Qlik's "nested set definitions"
and Qlik's "concat" function