Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Actually.. yeah, that works, and it's easier than telling business users to use set analysis. I can display counts another way.
Thanks!!
no problem!
when i went to reply to your new thread it said it no longer existed... O_o
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,
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?
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
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,
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';