Set Analysis for Charts - ignore selection for a Field and replace the value

[Anonymous]

Hi Qlikview Experts,

Tricky to explain in words; hence please see two tables I have loaded into the document:

ID	Data1	Set
AA1	350	A
BB1	200	B
TT4	500	A
WW1	250	A
VV4	400	B

Set	ID_2	Data2
A	AA1	8300
B	BB1	5520
A	TT4	1500
A	WW1	3550
B	VV4	2500

As you can see the fields for ID and ID_2 have the same values. What connects the two tables is Set.

There is a List Box available to the user to select ID. The Straight Table then displays ID_2, Data1, Data2. The objective is the straight table must show data belonging to ID_2. So the challenge here now is Data1: It would naturally display the value of Data1 for the selected ID. What I want is to display the value of Data1 for the resulting ID_2 line items that are in the same Set.

I tried something like:

=SUM({<ID={[ID_2]}>}Data1)

but does not do anything.

Appreciate the assistance. Note I am unable to modify the actual loaded tables as it will drastically affect all the code across the whole document; hence, hoping for a solution thru some form of set analysis or other syntax.

Please see attached sample document that mirrors the situation. Thanks!!!

[sasiparupudi1]

See if the attached document helps you?

basically I have created a calculated dimension where ID_2=ID, select ID

suppressed null values and in the presentation tab , hidden the column

hth

Sasi

[Anonymous]

Helpful, thanks, but I may have not been very clear in explaining what I was trying to accomplish. Essentially the desired resulting straight table, for example if the user selected BB1, should be:

ID_2	Data2	Data1
BB1	5520	200
VV4	2500	400

Selecting AA1 should then result in:

ID_2	Data2	Data1
AA1	8300	350
TT4	1500	500
WW1	3550	250

I've been trying to tweak further the changes you did on the dimensions/expressions but have not had any luck. Can you assist?

Thanks!

[sushil353]

Hi,

Please find attached.

HTH

Sushil

[sushil353 · Accepted solution]

done some changes.

[Anonymous]

Thanks Sushil, your attached document solves the issue. And thanks also Sasidhar for setting up the initial code.

I have another very similar scenario and when I try to adopt the same methodology, I can't get it to work. Simplified sample below:

Two tables loaded:

Location	ID	Data1
Area1	ABC	100
Area1	DEF	150
Area2	GHI	500
Area3	JKL	750
Area3	MNO	800

ID	NextID
ABC	DEF
ABC	GHI
DEF	ABC
GHI	ABC
GHI	JKL
GHI	MNO
JKL	GHI
JKL	MNO
MNO	GHI
MNO	JKL

List box will be available to user to select Location. If user selects Area1, the straight table should result in:

NextID	Data1
DEF	150
GHI	500
ABC	100

If the user selects Area2, the straight table should result in:

NextID	Data1
ABC	100
JKL	750
MNO	800

Hoping for assistance! Thanks!