Substitute the dimension [Straight Table] with another Field

[Anonymous]

Hi Qlikview Experts,

I have one table with two columns having different field names but having the same set of fields, [ID] and [NextID]. I have another table having [ID] and [Data1]. I am trying to create a Straight Table where the Dimension is [NextID] and the Expression is [Data1]. The values for [Data1] would be the values of [ID] where [ID] = [NextID]. Please see below to illustrate:

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

OBJECTIVE:

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

I have challenges correctly pulling [Data1].

Hoping for assistance! Thanks! Attached sample document.

[Anonymous]

I tried adopting something I learned from another thread by changing the expression for Data1 into =sum({<ID=p(NextID)>}Data1) but it is not working properly.

Any suggestions?

[Anonymous]

Still hitting a wall on this one.

Essentially the challenge here is that my dimension on the chart is on a Field, 'NextID' (which has the same set of values as another Field, 'ID') but the data on my expression is linked to the other Field, 'ID'.

How can I use 'NextID' as the dimension but at the same time pull 'Data1' as the expression respective to it as if the field, 'ID', was substituted with 'NextID'?

[MarcoWedel · Accepted solution]

Hi,

one solution could be:

table1:
LOAD * FROM [https://community.qlik.com/thread/180625] (html, codepage is 1252, embedded labels, table is @1);

table2:
LOAD * FROM [https://community.qlik.com/thread/180625] (html, codepage is 1252, embedded labels, table is @2);

table3:
LOAD ID as NextID,
    Data1 as NextData1
Resident table1;

hope this helps

regards

Marco

[Anonymous]

This checks out. Thank you for your help, sir!

[MarcoWedel]

You're welcome

Regards

Marco