Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

markramiro
Contributor II

Substitute the dimension [Straight Table] with another Field

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:

LocationIDData1
Area1ABC100
Area1DEF150
Area2GHI500
Area3JKL750
Area3MNO800

IDNextID
ABCDEF
ABCGHI
DEFABC
GHIABC
GHIJKL
GHIMNO
JKLGHI
JKLMNO
MNOGHI
MNOJKL

OBJECTIVE:

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

NextIDData1
DEF150
GHI500
ABC100

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

NextIDData1
ABC100
JKL750
MNO800

I have challenges correctly pulling [Data1].

Hoping for assistance! Thanks! Attached sample document.

1 Solution

Accepted Solutions

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

Hi,

one solution could be:

QlikCommunity_Thread_180625_Pic1.JPG

QlikCommunity_Thread_180625_Pic2.JPG

QlikCommunity_Thread_180625_Pic4.JPG

QlikCommunity_Thread_180625_Pic3.JPG

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

View solution in original post

5 Replies
markramiro
Contributor II

Re: Substitute the value of a dimension with another Field

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?

markramiro
Contributor II

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

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

Hi,

one solution could be:

QlikCommunity_Thread_180625_Pic1.JPG

QlikCommunity_Thread_180625_Pic2.JPG

QlikCommunity_Thread_180625_Pic4.JPG

QlikCommunity_Thread_180625_Pic3.JPG

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

View solution in original post

markramiro
Contributor II

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

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

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

You're welcome

Regards

Marco