topic Re: Link Table issue in QlikView

Link Table issue

Tue, 04 Sep 2012 19:08:46 GMT

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.

Can anyone assist??

Thanks very much.

Re: Link Table issue

MayilVahanan — Tue, 04 Sep 2012 19:15:51 GMT

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.

Re: Link Table issue

Tue, 04 Sep 2012 19:34:55 GMT

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!

Re: Link Table issue

MayilVahanan — Tue, 04 Sep 2012 20:06:44 GMT

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

Re: Link Table issue

Tue, 04 Sep 2012 20:10:10 GMT

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

Re: Link Table issue

pgalvezt — Tue, 04 Sep 2012 20:11:53 GMT

Use Autonumber Function()

Re: Link Table issue

MayilVahanan — Tue, 04 Sep 2012 20:14:12 GMT

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

Re: Link Table issue

Tue, 04 Sep 2012 20:14:45 GMT

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

Re: Link Table issue

Tue, 04 Sep 2012 20:22:39 GMT

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

Re: Link Table issue

Thu, 20 Sep 2012 18:19:18 GMT

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.