topic Linking 2 Dimension Tables with Link Table in QlikView

Linking 2 Dimension Tables with Link Table

benvatvandata — Tue, 11 Oct 2016 18:09:51 GMT

Hi,

In the past I've been able to add/join keys to an existing table by using a third table that has both keys in it, but what should the script look like if I just want an Item table, Customer table, and Link table? I've been working off examples found online, but I can't seem to get it to work even though this seems pretty basic...

For example:

Item:

LOAD

ItemCode,

CustCode,

ItemDesc

From

(qvd);

Customer:

LOAD

CustCode,

CustName

From

(qvd);

Link:

Any help is appreciated, thanks in advance!

-Ben

Re: Linking 2 Dimension Tables with Link Table

sunny_talwar — Tue, 11 Oct 2016 18:30:16 GMT

There is only one common field between the two table, why do you need a link table here?

Re: Linking 2 Dimension Tables with Link Table

benvatvandata — Tue, 11 Oct 2016 19:24:47 GMT

I need a link table because I will be adding more tables eventually, I just wanted to start simple.

Re: Linking 2 Dimension Tables with Link Table

sunny_talwar — Tue, 11 Oct 2016 20:36:18 GMT

Something like this:

Item:

LOAD

ItemCode,

CustCode,

ItemDesc

From

(qvd);

Customer:

LOAD

CustCode,

CustName

From

(qvd);

LinkTable:

LOAD ItemCode, //Assuming this is a unique Identifier in the table,

CustCode,

<Fields which are common in Item and Customer>

Resident Item;

Concatenate(LinkTable)

LOAD CustCode,

<Fields which are common in Item and Customer>

Resident Customer;

DROP Fields <Fields which are common in Item and Customer> FROM Item;

DROP Fields <Fields which are common in Item and Customer> FROM Customer;

Re: Linking 2 Dimension Tables with Link Table

benvatvandata — Tue, 11 Oct 2016 20:52:27 GMT

So this is what I have:

Item:

LOAD

ItemCode,

CustCode,

ItemDesc

From (qvd);

Customer:

LOAD

CustCode,

CustName

From (qvd);

Link:

LOAD

ItemCode,

CustCode

Resident Item;

Concatenate(Link)

LOAD

CustCode

Resident Customer;

Drop Field CustCode from Item;

However, the fields aren't matching up, for example: Assume CustCode = 123 owns ItemCodes 1,2,3...

It isn't linking these codes to this customer.

What am I doing wrong?

Thanks,

Ben

Re: Linking 2 Dimension Tables with Link Table

sunny_talwar — Tue, 11 Oct 2016 21:00:27 GMT

I am recently being out of touch with LinkTable and having a difficult time with imagining how this needs to look without an example. I think you can either look here: Link Table in QlikView – Learn QlikView. or provide a sample for me to test out

Re: Linking 2 Dimension Tables with Link Table

benvatvandata — Wed, 12 Oct 2016 13:27:15 GMT

Thanks for your help, after a little research I found out the CustCode was blank in my Item table... haha

-Ben

Re: Linking 2 Dimension Tables with Link Table

sunny_talwar — Wed, 12 Oct 2016 16:09:39 GMT

Hahahaha thanks for sharing that information . I was wondering what might have gone wrong.

Best,

Sunny

Re: Linking 2 Dimension Tables with Link Table

sudhakar_budde — Sun, 19 Aug 2018 15:57:57 GMT

Hi Sunny/Ben,

I have similar situation where I joined recently. I found that user was asking a developer to just add tables one after the other int the data model without having specific reporting requirement. Once the data model was built they were started using it for KPIs and dashboards. The developer created a 'link table' as exactly as you mentioned above. Please see the attached app. The problem is they started raising bugs that they see duplicate records with some null attributes as shown in my example! I thought this approach is not right, I prefer to create the data model as per the requirement of reports or at least identify the fact and appropriate association between the dimensions. If the link table is unavoidable , then create composite keys where appropriate.

Can you please share your thoughts and if there is a way of working around or user instructions to use the existing model instead of re- !engineering the model ?

Thanks for your help in advance.

Sudhakar