topic Re: Excluding names from a Listbox in QlikView

Excluding names from a Listbox

Tue, 09 Oct 2012 13:05:47 GMT

Hi All,

I'm struggling to exclude certain names from a Listbox I have created - Field is CVsSent.CONSULTANT. As it stands, the listbox is displaying every person's name in the company but i'd like to remove / hide a few of them if possible so they no longer feature in the box.

For example:

John

Paul

James

Sarah

Julia

Jon

Simon

Andrew

How would I go about removing Jon, Simon and Andrew so they are no longer displayed in the Listbox? I'm guessing by adding an expression in the ListBox properties but each time I do, it doesn't work. I think it's something basic but I'm very new to this.

Thanks so much,

Lindsay

Re: Excluding names from a Listbox

christophebrault — Tue, 09 Oct 2012 13:10:42 GMT

Hi,

I'm using this expression in listbox to do the same thing :

=AGGR(ONLY({<NodeName-={'AAAA','BBBBB','CCCC','.....',}>} NodeName),NodeName)

Replace Nodename with your field and write name in the set analysis

Re: Excluding names from a Listbox

Tue, 09 Oct 2012 13:13:16 GMT

Add the following expression in your listbox (assuming your fieldname is indeed CVsSent.CONSULTANT)

if(

CVsSent.CONSULTANT <>'Jon' and

CVsSent.CONSULTANT <>'Simon' and

CVsSent.CONSULTANT <>'Andrew' ,

CVsSent.CONSULTANT)

Note by the way that users may probably add their own listboxes (when viewing this application on the Accesspoint). Perhaps a thing to keep in mind while adding security (if needed)

Re: Excluding names from a Listbox

Tue, 09 Oct 2012 13:28:41 GMT

thanks for coming back to me. I've tried this but all names are still displayed and each name in the box now has a Null Symbol next to it. i.e. -

Expression now looks like this:

=AGGR(ONLY({<CVsSent.CONSULTANT-={'Jon','Simon','Andrew',}>} CVsSent.CONSULTANT),CVsSent.CONSULTANT)

Result is:

John -

Paul -

James -

Sarah -

Julia -

Jon -

Simon -

Andrew -

Re: Excluding names from a Listbox

Tue, 09 Oct 2012 13:31:27 GMT

Hi,

Make sure you _don't_ add this expression on the expression tab.

Add the expression in the following way: on the tab "general" of your listbox you see the dropdown where you choose which field should be active in the listbox. There use the lowest option, named "<Expression>". When selected, a dialogue popsup where one of our expressions should be placed to get it working 🙂

Hope it helps!

Re: Excluding names from a Listbox

Tue, 09 Oct 2012 13:33:53 GMT

Hi, thanks also for replying. I've done absolutely what you have suggested but now what happens is every name that i want to remain in the list is now duplicated and the ones i want to remove have a null symbol, i.e. - next to their names.

So it looks something like this:

Alex Alex

Jon -

Appreciate the help.

thx,

Re: Excluding names from a Listbox

Tue, 09 Oct 2012 13:38:50 GMT

Ok... Are you sure that the fieldname you use is correct? And/or there is no syntax error in your expression?

I added a sample qvw where both my expression is working and the aggr/only expression.

Hope it helps if you check the expression in this sample qvw...

If not, don't hesitate to let me know.

Re: Excluding names from a Listbox

Tue, 09 Oct 2012 13:43:20 GMT

You're absolutely right!! I was going to List Box Properties and going straight to the Expressions tab, then 'Add'. I've been pulling my hair out on this one for the whole morning lol so thank you.

Could you explain what the difference is between doing it the way you suggested and the way i tried. i.e. why does it work your way and not through the expressions tab?

Thanks again.

Re: Excluding names from a Listbox

christophebrault — Tue, 09 Oct 2012 13:50:27 GMT

Because expression tab add an expression using your listbox as a dimension.

It's like a straight table.

Using an expression instead of a field for your listbox will change the dimension.

Re: Excluding names from a Listbox

Tue, 09 Oct 2012 13:50:45 GMT

I can imagine you immediately went to the expressions tab.

I think it works as follows:

- the place I suggested is the (only) place to define which attribute (or expression) is the attribute for your listbox

- the place on the expression tab is a way to add extra information.

An example is that in the expression-tab you add count(FIELDNAME) which is the same as the checkbox "show frequency" that you have on the "general tab"...

I can imagine that sometimes you have a more complex expression than a count... so in that case you can do what you want on the "expressions tab"

PS: I'm curious what your hairdo looks like currently 😛

Re: Excluding names from a Listbox

Tue, 09 Oct 2012 14:22:02 GMT

It's now growing back 🙂

In terms of applying the same expression to a chart, would I be right in saying that I don't go to the Expressions tab and add this as a new expression...?

currently i've just got Count (CVsSent.CONSULTANT)

Re: Excluding names from a Listbox

Tue, 09 Oct 2012 14:41:38 GMT

So in your chart you count cvssent.consultant

But What is your dimension?

Re: Excluding names from a Listbox

Tue, 09 Oct 2012 14:53:41 GMT

I hope you didn't miss the bit that said i was new to this 😉

Used Dimensions is currently CVsSent.CONSULTANT

I'm assuming that's where you mean?

Re: Excluding names from a Listbox

Tue, 09 Oct 2012 16:44:57 GMT

Hi huncie247,

Yes, that's basically what I mean. But that would mean that your chart shows a count of CVsSent.Consultant per CVsSent.Consultant. Does that make sense?

In that case, your dimension can be a calculated dimension (just click edit on the dimension tab and paste there the same code as you put in your listbox). Your expression can stay "as is"

Re: Excluding names from a Listbox

Thu, 11 Oct 2012 14:57:57 GMT

Hi Roberto, been out of the office for the last few days and just come back to this.

Yep, that's great and has worked. For some reason though, my chart shows 8 names in a bar chart format which is fine and expected, but there is now a 9th bar at the end which appears to be a view of the total count of all 8 names. No name appears under it, just a - symbol.

Similarly, if i hover the mouse over each bar, it displays the "if" expression and then the total number. i.e.

if(

CVsSent.CONSULTANT <>'Jon' and

CVsSent.CONSULTANT <>'Simon' and

CVsSent.CONSULTANT <>'Andrew' ,

CVsSent.CONSULTANT)

=154

Thx,

Re: Excluding names from a Listbox

Thu, 11 Oct 2012 15:07:23 GMT

Hi,

You're executing a Count (CVsSent.CONSULTANT) against the calculated dimension that you state above...

That means that this count will be executed for all consultants except Jon,Simon,Andrew.

I guess that the 9th bar is the count for these 3 guys.

You can do 2 things...

1: add an "others" group

You could change the expression so that those 3 names are included in the "others" group. Then use this as your calculated dimension:

if(

CVsSent.CONSULTANT <>'Jon' and

CVsSent.CONSULTANT <>'Simon' and

CVsSent.CONSULTANT <>'Andrew' ,

CVsSent.CONSULTANT

//here the else statement...

'Others'

)

2: Hide that 9th bar

The 9th bar IMO exists because apparently Jon,Siman and/or Andrew also have cvsSent (so a count for them results in I guess 154). But the dimension is NULL (because of your calculated dimension

On the dimensions tab you could check the checkbox "suppress when value is null"

>>PS: I would double check if the right things are suppressed (should be the cvsSentconsultant for the 3 excluded persons...

Re: Excluding names from a Listbox

Thu, 11 Oct 2012 15:20:44 GMT

yep perfect! now all displaying correctly. do you have an email address i contact you directly on next time i need some help? as i said i'm very new to this and there will be a few things no doubt, as long as you don't mind assisting...?

Re: Excluding names from a Listbox

Thu, 11 Oct 2012 15:34:34 GMT

Hmz, I didn't plan to put my mailadress here on the forum. My mailbox may then become overloaded with requests (which would perhaps be interesting when I could send invoice for the spent hours 🙂 )

Lets not do that and keep it to helping eachother on this forum. Then even others can check the proposed solution and add their thoughts and comments.

Happy Qlikviewing!

Re: Excluding names from a Listbox

Thu, 11 Oct 2012 15:36:13 GMT

lol, i thought that afterwards actually. no worries and thanks for your help to date!

Re: Excluding names from a Listbox

Thu, 11 Oct 2012 15:37:18 GMT

No problem. I was just kidding. I'm glad I could help, and am happy for the times that I received help.

Bye!