Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

benvatvandata
New Contributor III

Linking 2 Dimension Tables with Link Table

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

1 Solution

Accepted Solutions

Re: Linking 2 Dimension Tables with Link Table

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;

8 Replies

Re: Linking 2 Dimension Tables with Link Table

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

benvatvandata
New Contributor III

Re: Linking 2 Dimension Tables with Link Table

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

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
New Contributor III

Re: Linking 2 Dimension Tables with Link Table

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

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
New Contributor III

Re: Linking 2 Dimension Tables with Link Table

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

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

Best,

Sunny

sudhakar_budde
Contributor

Re: Linking 2 Dimension Tables with Link Table

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 ?

Duplicate-records.PNG

Thanks for your help in advance.

Sudhakar

Community Browser