Linking 2 Dimension Tables with Link Table

[benvatvandata]

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

[sunny_talwar]

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

[benvatvandata]

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

[sunny_talwar · Accepted solution]

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;

[benvatvandata]

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

[sunny_talwar]

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

[benvatvandata]

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

-Ben

[sunny_talwar]

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

Best,

Sunny

[sudhakar_budde]

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