Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
There is only one common field between the two table, why do you need a link table here?
I need a link table because I will be adding more tables eventually, I just wanted to start simple.
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;
So this is what I have:
Item:
LOAD
ItemCode,
CustCode,
ItemDesc
From
Customer:
LOAD
CustCode,
CustName
From
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
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
Thanks for your help, after a little research I found out the CustCode was blank in my Item table... haha
-Ben
Hahahaha thanks for sharing that information . I was wondering what might have gone wrong.
Best,
Sunny
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