topic Re: Selecting the same value in two fields in QlikView

Selecting the same value in two fields

Mon, 23 Mar 2015 17:38:54 GMT

Hi everyone,

I have a list of about 100 values that appear in two fields. I want to be able to select one of those values and sum all Amounts if that value appears in either field. I'd also want to avoid any double-counting (if the value was present in both fields).

E.g., the data would look like:

Field1	Field2	Amounts
A	B	20
B	C	2
C	A	10
A	A	5
D	F	10

And if I selected "A", the red amounts would be selected. What kind of expression would I have to write to make this possible?

Re: Selecting the same value in two fields

Gysbert_Wassenaar — Mon, 23 Mar 2015 18:21:52 GMT

See attached qvw.

Re: Selecting the same value in two fields

Mon, 23 Mar 2015 19:56:25 GMT

This is awesome! Thank you for introducing me to P()

However, one more question - how can I create a list box for those 100 values for users to select, rather than have them search in list boxes for Field1 or Field2?

I saw a solution using "=Field1 & '-' & Field2", but that creates a combination. I would rather the list box was just

and their selection used your expression across Field1 and Field2.

Re: Selecting the same value in two fields

Mon, 23 Mar 2015 20:49:38 GMT

And another one:

- sum ONLY when selected values are BOTH in Field1 and Field2

Re: Selecting the same value in two fields

Mon, 23 Mar 2015 21:18:06 GMT

I noticed one problem with your solution - I actually have other fields, and making selections in them seems to affect this expression. How do I prevent that from happening?

Re: Selecting the same value in two fields

Gysbert_Wassenaar — Tue, 24 Mar 2015 08:54:00 GMT

Create an alternate state on the General tab of the Document Properties window. Then assign the new alternate state to the straight table on the General tab of its properties window.

Re: Selecting the same value in two fields

Tue, 24 Mar 2015 13:14:58 GMT

Of course... thank you