topic Re: List box expression set analysis? in QlikView

List box expression set analysis?

Thu, 27 Jun 2013 14:26:14 GMT

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

Re: List box expression set analysis?

Thu, 27 Jun 2013 14:35:52 GMT

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.

Re: List box expression set analysis?

Thu, 27 Jun 2013 14:43:17 GMT

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

Thanks!!

Re: List box expression set analysis?

Thu, 27 Jun 2013 14:46:49 GMT

no problem!

Re: List box expression set analysis?

Thu, 27 Jun 2013 15:14:10 GMT

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

Re: List box expression set analysis?

gshockxcc — Thu, 25 Jun 2015 21:40:57 GMT

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,

Re: List box expression set analysis?

Tue, 30 Jun 2015 15:05:21 GMT

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?

Re: List box expression set analysis?

Tue, 30 Jun 2015 15:15:29 GMT

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

Re: List box expression set analysis?

gshockxcc — Wed, 01 Jul 2015 13:44:21 GMT

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,

Re: List box expression set analysis?

Wed, 01 Jul 2015 14:33:59 GMT

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';

Re: List box expression set analysis?

pratap6699 — Wed, 01 Jul 2015 16:06:02 GMT

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