Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

How can I include limited data from other filters while a filter is selected?

Hello everyone! I have kind of an odd question here. I have a few status that are Red, Yellow, and Green. I have a Straight Table that is displaying just the red status items. The items are then narrowed down based on the user's filter selection, however, one of the filter selections needs to be different. I need this filter selection to include data from the other selections without having to select them. So let's say I have filters A, B, C, and D the data table is currently taking the selection and limiting it to whichever is chosen as it should, but when I select A, I need it to display data from B, C, and D as well. How do I go about this? I have been researching and found things such as using Only, but I don't want all the data, just the red like I had before. So here is code I was working on, but it is just giving me the data under the selected filter:

 

=if(GetFieldSelections(Filter)='A',
if(Status='Red1' or
Status='Red2'
or
Status='Red3'
or
Status='Red4'
or
Status='Red5'
or
Status='Red6'
, Only({<Filter=>} Item)),
if(Status='Red1' or
Status='Red2'
or
Status='Red3'
or
Status='Red4'
or
Status='Red5'
or
Status='Red6'
, Item))

This is basically trying to say, if the A filter is selected, then if the status is red, show me the Items without the filter then if the filter is not A just limit to the red status items. This almost works, but it still only shows me data for the A filter instead of from all of them. Does anyone know how to get this to work properly or what I am missing? Thanks for your time!

1 Solution

Accepted Solutions
Highlighted

Maybe this?

if(GetFieldSelections(Genre) = 'Action/Adventure' and not(GetFieldSelections([On PS4]) = 'Y' or GetFieldSelections([On PSVita]) = 'Y' or GetFieldSelections([On Switch]) = 'Y' or GetFieldSelections([On XboxOne]) = 'Y'),

     only({<Genre =, [On PS4] = {'N'}>+<Genre =, [On PSVita] = {'N'}>+<Genre =, [On Switch] = {'N'}>+<Genre =, [On XboxOne] = {'N'}>}Title),

     only({<Genre =>}Title)

)

View solution in original post

8 Replies
Highlighted

Can you please post some example data as well as the output you'd like to see from that data?

Highlighted
Contributor
Contributor

Alright I have attached an example of what I am trying to do. The expression for the straight table is the following:

=if(GetFieldSelections(Genre)='Action/Adventure',
if([On PS4]='N' or
[On PSVita]='N' or
[On Switch]='N' or
[On XboxOne]='N', Only({<Genre=>}Title)),
if([On PS4]='N' or
[On PSVita]='N' or
[On Switch]='N' or
[On XboxOne]='N', Title))

So what I would like to happen is when the user selects Action/Adventure in the list box that the straight table still shows all the data outside of the Action/Adventure genre. So if the user chooses Action/Adventure, it shows all genres that have a 'N' in any of the On PS4, On PSVita, On Switch, or On XboxOne columns regardless of the genre. Does that make sense?

Highlighted

Try this as your expression:

only({<Genre =, [On PS4] = {'N'}>+<Genre =, [On PSVita] = {'N'}>+<Genre =, [On Switch] = {'N'}>+<Genre =, [On XboxOne] = {'N'}>}Title)

If it doesn't work as expected, let me know what rows should/should not be showing.

Highlighted
Contributor
Contributor

I think we are on the right track! I added an if statement to make it only for when the Genre = Action/Adventure:

=if(GetFieldSelections(Genre)='Action/Adventure',
only({<Genre =, [On PS4] = {'N'}>+<Genre =, [On PSVita] = {'N'}>+<Genre =, [On Switch] = {'N'}>+<Genre =, [On XboxOne] = {'N'}>}Title),
if([On PS4]='N' or
[On PSVita]='N' or
[On Switch]='N' or
[On XboxOne]='N', Title))

So all records that are supposed to show do show when the filter for Action/Adventure is applied, but the part that I need now is to filter any of the On PS4, On PSVita, On Switch, or On XboxOne by 'Y' that are Action/Adventure. We have gotten rid of any record that would have a 'Y' in every column, but is it possible to make it still filter on the 'Y' since they are still showing in the row? Basically I want to not show any records where 'Y' is on every column, but still want to filter on the 'Y' if it is in a row where one of the other shows 'N'.

Highlighted

I don't understand what mine isn't doing that you need it to do (the way I had it without your if statement).  The only rows it's showing are ones with an 'N' in one of those fields.  Can you please elaborate further on what specific rows are showing using my expression that should/should not be?  I don't think I'm going to be able to wrap my head around this problem without specific row examples.

Highlighted
Contributor
Contributor

Yes your statement is working. I added the if statement because it needed to be narrowed down to work only when Action/Adventure is selected. What I wanted now was to know if there is a way we can still filter by 'Y'. If you click on a 'Y' with your statement nothing happens. If a row as a Y on On PS4, On PSVita, On Switch, and On XboxOne, then it is not shown which is exactly what I want, however, I want to still be able to click a Y and it filter that column with Y. So the first record has a Y with On PS4, therefore it shows, but if I clicked on the Y to filter just for Y for On PS4 column, then nothing happens. Is it possible to filter on the Y's of column with a Genre selected?

Highlighted

Maybe this?

if(GetFieldSelections(Genre) = 'Action/Adventure' and not(GetFieldSelections([On PS4]) = 'Y' or GetFieldSelections([On PSVita]) = 'Y' or GetFieldSelections([On Switch]) = 'Y' or GetFieldSelections([On XboxOne]) = 'Y'),

     only({<Genre =, [On PS4] = {'N'}>+<Genre =, [On PSVita] = {'N'}>+<Genre =, [On Switch] = {'N'}>+<Genre =, [On XboxOne] = {'N'}>}Title),

     only({<Genre =>}Title)

)

View solution in original post

Highlighted
Contributor
Contributor

Aha! That did it! I can not thank you enough for your help with this weird question! Seriously thank you so so much!