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: 
Not applicable

List box expression set analysis?

My data model has a table in it.  My user is only interested in a certain subset of that table's data (WHERE FieldA = 'XYZ').  Originally, I handled this in the script by adding another table by doing a resident load of the table with FieldA = 'XYZ' in the WHERE clause.  This works, but I it would be better to not have an extra table in the data model, and instead use set analysis.

This works great for charts, but I'm having issues with list boxes.  I want the list box to only contain values where FieldA = 'XYZ'.  I can build an expression for the list box, and do something like =Only({<FieldA = {'XYZ'}>} ListBoxField), but Only isn't the right command.  I also don't think you can show frequencies for expressions.. but I'd be happy even just to get the possible selections.

How am I supposed to do this?

Thanks

1 Solution

Accepted Solutions
Not applicable
Author

Try an If Statement in the expression instead...

I have a tab for example where i only want to see data involved with Canada, so in my listbox of countries, i created an if statement within the expression field which now only shows Canada.

=if([Country Name] ='Canada', [Country Name])

you can add as many fields as you need with 'or' and 'and' functions.

hope this helps.

View solution in original post

10 Replies
Not applicable
Author

Try an If Statement in the expression instead...

I have a tab for example where i only want to see data involved with Canada, so in my listbox of countries, i created an if statement within the expression field which now only shows Canada.

=if([Country Name] ='Canada', [Country Name])

you can add as many fields as you need with 'or' and 'and' functions.

hope this helps.

Not applicable
Author

Actually.. yeah, that works, and it's easier than telling business users to use set analysis.  I can display counts another way.

Thanks!!

Not applicable
Author

no problem!

Not applicable
Author

when i went to reply to your new thread it said it no longer existed... O_o

gshockxcc
Creator
Creator

HI Parrish,

   I have a list box where I want to use this same set analysis, but I have "A_...", "B_...", and "C_..."

Suppose I want to filter so that only those fields with "C_" are visible.  How would I modify your suggestion to work in my case?

Thanks in advance!

Best regards,

Not applicable
Author

hi Kristan, sorry for the delayed response. let me confirm what you are asking - instead of showing 1 field in a list box (ie. USA), you want to be able to show 3 (ie. USA, CA, SA)? and then you want to be able to toggle back and forth based on which of the 3 you want to see?

Not applicable
Author

i think that is what you are asking

that being said, this is how i would write an expression to show 2 or more values instead of just 1:

=if([Country Name] ='Canada', or [Country Name]='USA' or [Country Name]='South America', [Country Name])

then all 3 of those countries would show up in my list box and if i just wanted to see USA, i would just click it and my data would drill down to only show USA.

Hope this helps!

Thanks,

Parrish

gshockxcc
Creator
Creator

Parrish,

     Thanks for getting back to me.  I didn't envision implementing it as you have described, but essentially, that's what I am looking to do.  Once I have developed the functionality in a list box, my goal is to move this to the script where it checks to see if these fields are present or not, and removes them where appropriate.

Thanks much.

Regards,

Not applicable
Author

Hey Kristan,

So essentially, you could do the exact same thing in the script - depending on which data source houses A, B and C, you could write something like this in the script: When you use a 'Where Statement' in the script, you can filter the data during the load instead of having QlikView do this in the design view.

TABLE NAME:

LOAD  
    
MATERIAL_NUMBER as MATERIAL,
    
MaterialDesc as DESCRIPTION,
    
Brand as BRAND
FROM
[\\NETWORK\QLIKVIEW\FILES\QVD\MATERIAL.qvd]
(
qvd)
Where Brand = 'A' or Brand = 'B' or Brand = 'C';