topic Re: Substitute the value of a dimension with another Field in QlikView

Substitute the dimension [Straight Table] with another Field

Anonymous — Mon, 14 Sep 2015 16:07:57 GMT

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.

Re: Substitute the value of a dimension with another Field

Anonymous — Tue, 15 Sep 2015 16:46:57 GMT

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?

Re: Substitute the dimension [Straight Table] with another Field

Anonymous — Wed, 16 Sep 2015 23:17:08 GMT

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'?

Re: Substitute the dimension [Straight Table] with another Field

MarcoWedel — Wed, 16 Sep 2015 23:43:43 GMT

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

Re: Substitute the dimension [Straight Table] with another Field

Anonymous — Sun, 27 Sep 2015 09:58:08 GMT

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

Re: Substitute the dimension [Straight Table] with another Field

MarcoWedel — Sun, 27 Sep 2015 10:34:46 GMT

You're welcome

Regards

Marco

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

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

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