topic Re: What is a link table?? in QlikView

What is a link table??

nikhilgarg — Wed, 24 Sep 2014 09:03:19 GMT

Hey,

Can anyone tell me what is a link table and how it is used ??

Thanks

Re: What is a link table??

richard_pearce6 — Wed, 24 Sep 2014 09:06:05 GMT

Interview question?

A table purley of keys linking two or more fact tables.

Richard

Qlikcentral.com

Re: What is a link table??

Wed, 24 Sep 2014 09:09:16 GMT

hi,

linktable is resloving tha synthatic keys & loops.

syn:

For Eample :

sno&', '&name&' ,' as Name

actuval table:

sno name

1 hh

2 mm

3 nn

4 gg

5 ll

Result:

Name

1,hh

2,mm

3,nn

4,gg

5,ll

***

When u have Multiful Fact tables in qlikview we can use linktable. when u they are joined with different Dimension table that time we 'll use linktable.

Re: What is a link table??

nikhilgarg — Wed, 24 Sep 2014 09:12:48 GMT

Hey,

How can i make link table??

Can you please tell.

Thanks.

Re: What is a link table??

richard_pearce6 — Wed, 24 Sep 2014 09:13:38 GMT

usually you would re-load tables you have and join / concat them in a way to create the link table.

Re: What is a link table??

ychaitanya — Wed, 24 Sep 2014 09:15:49 GMT

Refer to Link table

Re: What is a link table??

jagan — Wed, 24 Sep 2014 09:22:15 GMT

Hi,

Check below script

Fact1:

LOAD

Dim1 & '_' & Dim2 & '_' &Dim3 AS Key,

Measure1,

Measure2

FROM FactTable1;

Fact2:

LOAD

Dim1 & '_' & Dim2 & '_' &Dim3 AS Key,

Measure3,

Measure4

FROM FactTable2;

LinkTable:

LOAD DISTINCT

Dim1 & '_' & Dim2 & '_' &Dim3 AS Key,

Dim1,

Dim2,

Dim3

FROM FactTable1;

Concatenate(LinkTable)

LOAD DISTINCT

Dim1 & '_' & Dim2 & '_' &Dim3 AS Key,

Dim1,

Dim2,

Dim3

FROM FactTable1

WHERE NOT Exists(Key, Dim1 & '_' & Dim2 & '_' &Dim3);

Hope this helps you.

Regards,

Jagan.

Re: What is a link table??

er_mohit — Wed, 24 Sep 2014 09:22:15 GMT

Have a look to this

DailyQV: Link tables

Re: What is a link table??

sujeetsingh — Wed, 24 Sep 2014 09:24:45 GMT

Link tables are used to link two tables with multiple key like a bridge.

Re: What is a link table??

er_mohit — Wed, 24 Sep 2014 09:26:11 GMT

Have a look to this

DailyQV: Link tables

Re: What is a link table??

Anonymous — Wed, 24 Sep 2014 09:27:22 GMT

u forgot to write drop command at last

Re: What is a link table??

nikhilgarg — Wed, 24 Sep 2014 09:27:46 GMT

HEy,

Can you please tell me how this link table resolve the synthetic keys and loops by an example ??

I mean i dont know how to make a link table . So can you tell me ??

Thanks.

Re: What is a link table??

nikhilgarg — Wed, 24 Sep 2014 09:32:29 GMT

Hey,

Sorry but i did not get what you are trying to do above specially after Link Table. Why you load same data twice with where not exist clause because i think key = Dim1 & '_' & Dim2 & '_' &Dim3 because Dim1 & '_' & Dim2 & '_' &Dim3 AS Key,

Kindly explain.

Thanks

Re: What is a link table??

jagan — Wed, 24 Sep 2014 09:57:13 GMT

Hi Nikhil,

Sorry, Typing mistake. Link table is used whenever we have multiple dimensions in two or more fact tables. When we load this in Qlikview Synthetic keys will be formed, with synthetic keys the performance would be affected, so avoid this Link table is one of the technique, other than link table we can also use Concatenate.

Fact1:

LOAD

Dim1 & '_' & Dim2 & '_' &Dim3 AS Key,

Measure1,

Measure2

FROM FactTable1;

Fact2:

LOAD

Dim1 & '_' & Dim2 & '_' &Dim3 AS Key,

Measure3,

Measure4

FROM FactTable2;

LinkTableTemp:

LOAD DISTINCT

Dim1 & '_' & Dim2 & '_' &Dim3 AS Key,

Dim1,

Dim2,

Dim3

FROM FactTable1;

Concatenate(LinkTableTemp)

LOAD DISTINCT

Dim1 & '_' & Dim2 & '_' &Dim3 AS Key,

Dim1,

Dim2,

Dim3

FROM FactTable2;

LinkTable:

Noconcatenate

LOAD DISTINCT *

FROM LinkTableTemp;

DROP TABLE LinkTableTemp;

Hope this heps you.

Regards,

Jagan.

Re: What is a link table??

nikhilgarg — Wed, 24 Sep 2014 10:08:57 GMT

Hey,

Sorry but i still did not get usage of Link table Completely.

In above example, you have Dim1 , 2 , 3 , measure1 , 2 , 3 , 4 as fields or columns that may contain duplicate 'key' . Ryt ??

If yes then , in facts table you concatenate Dim1 , 2 , 3 as key.

Then in LinkTableTemp:

You are loading distinct values of key and dim1 , 2 , 3 from facttable1 and facttable2 . Ryt ??

If yes , then why is it so ??

I dont understand usage of :

LinkTable:

Noconcatenate

LOAD DISTINCT *

FROM LinkTableTemp;

and also no use of Fact1 and Fact2 table.

Kindly explain.

Thanks.

Re: What is a link table??

Wed, 24 Sep 2014 10:25:14 GMT

Sample of using multiple dates and letting the user decide what date to use.

You have an Invoice table and a Orders table. Both have a date field and you would like to have only one set of date fields on your sheet (f.i. Year, Month, Day). You load (concatenate both) the key plus the date from each table, plus an indicator ('Invoice" AS Type; 'Order' AS Type) into a link table and rename the date to a single date field (f.i. Invoice_Date AS Link_Date; Order_Date AS Link_Date). You also have acalender wher the date is renamed (or copied) to that link date (Calendar_Date AS Link_Date). Now you can use the Calender fields to select either Order dates or Invoice dates. The field Type is used to determine what dates are being used.

Something like this ...

Invoice:

%KEY_Invoice,

Invoice_Date,

...

Order:

%KEY_Order,

Order_Date,

...

LINK:

LOAD

%KEY_Invoice,

Invoice_Date AS Link_Date,

'Invoice' AS Type

RESIDENT Invoice;

CONCATENATE

%KEY_Order,

Order_Date AS Link_Date,

'Order' AS Type

RESIDENT Order;

Calendar:

Year,

Month,

Quarter,

Date,

Date AS Link_Date,

...

Invoice and Orders link to the LINK table and the LINK table links to the Calendar table.

Re: What is a link table??

jagan — Thu, 25 Sep 2014 01:30:03 GMT

Hi,

It is a Datawareshouse topic go through the datawarehousing concepts which helps you in learning this type of concepts.

Check this links

What is Link Tables? What is the use?

http://www.learnallbi.com/link-table-in-qlikview/

http://www.qliktips.com/2011/05/rules-for-creating-keylink-table-in.html

http://www.tdan.com/view-articles/5172/

Regards,

Jagan.

Re: What is a link table??

Siva_Sankar — Thu, 25 Sep 2014 03:12:36 GMT

Nikhil,

For a deeper understanding find the attached link tables document. Gives you an explanation with examples.