Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Link Table issue

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.  

9 Replies

Re: Link Table issue

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.

Not applicable

Re: Link Table issue

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

Re: Link Table issue

Hi

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

Not applicable

Re: Link Table issue

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
Valued Contributor

Re: Link Table issue

Use Autonumber Function()

Re: Link Table issue

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..

Not applicable

Re: Link Table issue

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

Not applicable

Re: Link Table issue

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

Re: Link Table issue

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.  

Community Browser