topic Re: How do I exclude values from a List Box using Alternate States? in QlikView

How do I exclude values from a List Box using Alternate States?

cliff_clayman — Thu, 03 Nov 2016 23:47:17 GMT

I have two list boxes with the same dimension. When I make a selection in the first list box, I do not want that same value available for selection in the second list box. How can I do this?

Re: How do I exclude values from a List Box using Alternate States?

sunny_talwar — Thu, 03 Nov 2016 23:49:51 GMT

Try this

List Box 1

FieldName

List Box 2

Aggr(Only({1<FieldName = p(FieldName)>}FieldName), FieldName)

Re: How do I exclude values from a List Box using Alternate States?

cliff_clayman — Fri, 04 Nov 2016 00:04:27 GMT

That doesn't seem to work right for me. In the above example, I chose AAA Running Shoe. I do not want to be able to select the same value in the second list box.

Re: How do I exclude values from a List Box using Alternate States?

sunny_talwar — Fri, 04 Nov 2016 00:15:17 GMT

Is this not working?

Aggr(Only({1<ProductName = e(ProductName)>}ProductName), ProductName)

Apologizes, so are you using two states for this?

Re: How do I exclude values from a List Box using Alternate States?

cliff_clayman — Fri, 04 Nov 2016 00:17:23 GMT

Yes, I apologize for not making that clear. I called them Group1 and Group2. I just don't want to be able to compare the same product to each other in the chart.

Re: How do I exclude values from a List Box using Alternate States?

sunny_talwar — Fri, 04 Nov 2016 00:26:33 GMT

I have not tested this, but try this

ProductName -> Group1

Aggr(Only({1<ProductName = e({Group1} ProductName)>} ProductName), ProductName) -> Group2

Re: How do I exclude values from a List Box using Alternate States?

cliff_clayman — Fri, 04 Nov 2016 00:32:07 GMT

It somewhat works. The second list box starts out without any values and somehow when nothing is selected in the second list box, I still get some dollar values in the chart.

Re: How do I exclude values from a List Box using Alternate States?

sunny_talwar — Fri, 04 Nov 2016 00:36:11 GMT

Try this:

=Aggr(If(GetSelectedCount(ProductName, False(),'Group1') = 0, ProductName, Only({1<ProductName = e({Group1} ProductName)>} ProductName)), ProductName)

Re: How do I exclude values from a List Box using Alternate States?

cliff_clayman — Fri, 04 Nov 2016 00:39:38 GMT

Yeah, that seems to work! Can you explain what the expression is doing exactly?

Re: How do I exclude values from a List Box using Alternate States?

sunny_talwar — Fri, 04 Nov 2016 01:32:23 GMT

When nothing is selected then show ProductName

=Aggr(If(GetSelectedCount(ProductName, False(),'Group1') = 0, ProductName, Only({1<ProductName = e({Group1} ProductName)>} ProductName)), ProductName)

When a ProductName is selected in Group1, then exclude whatever is selected in Group1's productname field

=Aggr(If(GetSelectedCount(ProductName, False(),'Group1') = 0, ProductName, Only({1<ProductName = e({Group1} ProductName)>} ProductName)), ProductName)

Re: How do I exclude values from a List Box using Alternate States?

cliff_clayman — Fri, 04 Nov 2016 15:30:16 GMT

This is probably not possible or not relevant, but can it work the other way around meaning if someone clicks something in Group2 first?

Re: How do I exclude values from a List Box using Alternate States?

Fri, 04 Nov 2016 16:10:07 GMT

Use the below in the 1st list box

=Aggr(If(GetSelectedCount(ProductName, False(),'Group2') = 0, ProductName, Only({1<ProductName = e({Group2} ProductName)>} ProductName)), ProductName)

Re: How do I exclude values from a List Box using Alternate States?

sunny_talwar — Fri, 04 Nov 2016 16:10:27 GMT

Unable to test this because I have QV11 SR5 installed on my machine at work, but try this out

=Aggr(If(GetSelectedCount(ProductName, False(),'Group2') = 0, ProductName, Only({1<ProductName = e({Group2} ProductName)>} ProductName)), ProductName)

Re: How do I exclude values from a List Box using Alternate States?

sunny_talwar — Sun, 06 Nov 2016 05:16:02 GMT

Actually, when I tested, I saw a slight loophole in the expression.

Try these:

=Aggr(If(GetSelectedCount(ProductName, False(),'Group2') = 0, Only({1} ProductName), Only({1<ProductName = e({Group2} ProductName)>} ProductName)), ProductName)

=Aggr(If(GetSelectedCount(ProductName, False(),'Group1') = 0, Only({1} ProductName), Only({1<ProductName = e({Group1} ProductName)>} ProductName)), ProductName)

Attached the updated qvw with this email.

Best,

Sunny