Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Link Table issue

Hello, Can anyone please explain how to deail with this key issue? 

Example:

Table1

  ID1   (primary key)

  ID2

  Value1

Table2

  ID1

  ID2

  Value2

Table3

  ID2  (primary key)

  Value3

Unfortunately in this case I can not join or contatenate the tables.  So I really need to use a Link table.  I attempted to concatenate ID1&" " &ID2 into a link table, which worked fine, but not sure how to now incorporate ID2 from Table3 into the link table without creating another loop.

Here is a picture of the 3 tables before applying any fixes showing the QV created Symbolic links.

Capture.PNG

Can anyone assist??

Thanks very much.

1 Solution

Accepted Solutions
Not applicable
Author

So I resolved this with a key table.  Thanks everyone for your assistance.   For Table 1, I created a key table by combining indexes ID1 and ID2 into 1 index field (Join Load ID1&' '&ID2 as ID12Index) along with the original index values from each of the tables.  Then repeated the process for Table 2 using an OUTER JOIN.  This was critical in resolving the issue.   A Concatenate Join did NOT work because data was duplicated resulting in multiple rows for each index.  This possibly could have worked but all applicationg expressions would have needed to be modified.   Using and OUTER JOIN maintained all the data and put the indexes onto the same rows so no changes were required to the expressions.  

View solution in original post

9 Replies
MayilVahanan

Hi

     Try like this,

     Load ID1 as pk,ID2 , value1 from table1;

          Load ID1 as pk,ID2 as fk, value2 from table2;

          Load ID2 as fk, value3 from table3;

This also one of way to avoid looping.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

Thanks very much for the response!   This is close, but using this scenario does not have a link from table 3 to table 1, so clicking a value in table 3 provides incorrect results in table 1.  (although table 2 works perfectly)

Here is the table layout when I try your recommendation along with what some sample data looks like in QV.   Please let me know if there is a way to get this to work and THANK YOU!

Capture1.PNG

Capture2.PNG

MayilVahanan

Hi

     Did you try join or concatenate method for table?     That bring all three table into one.. I think its helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

I am trying to avoid concatenating or joining the tables in this particular scenario.   It seems like the must be some way to do this with a link table....but maybe not......?

pgalvezt
Specialist
Specialist

Use Autonumber Function()

MayilVahanan

Hi

     Both table1 and table3 have same row. Ok. May be try like this..in Both table1 and table3, create a field -> rowNo() as Key..

Its join the 1st n 3rd table..without looping creation..

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

Sorry, could you provide a bit more detail?  Which table(s)? 

Not applicable
Author

The sample data I used above is unfortunately random.  So the rows will not match up in table 1 and table 2, just in my example they do.  I probably should have used different data in Table3 column ID2FK (like 10,11,12, etc).

Not applicable
Author

So I resolved this with a key table.  Thanks everyone for your assistance.   For Table 1, I created a key table by combining indexes ID1 and ID2 into 1 index field (Join Load ID1&' '&ID2 as ID12Index) along with the original index values from each of the tables.  Then repeated the process for Table 2 using an OUTER JOIN.  This was critical in resolving the issue.   A Concatenate Join did NOT work because data was duplicated resulting in multiple rows for each index.  This possibly could have worked but all applicationg expressions would have needed to be modified.   Using and OUTER JOIN maintained all the data and put the indexes onto the same rows so no changes were required to the expressions.