topic Re: syn keys in QlikView

syn keys

Sat, 12 Sep 2015 15:49:50 GMT

Do I need to remove the syn.key from these tables?
If so where do I start?

Re: syn keys

sunny_talwar — Mon, 14 Sep 2015 04:57:18 GMT

You don't have to remove synthetic keys, but having them makes your data model a little complicated to comprehend. Look at this blog:

HTH

Best,

Sunny

Re: syn keys

Mon, 14 Sep 2015 05:05:02 GMT

Hi,

check with this it might help u.

https://community.qlik.com/search.jspa?peopleEnabled=true&userID=&containerType=&container=&spotlight=true&q=remove%20sy…

Re: syn keys

Mon, 14 Sep 2015 05:06:48 GMT

Hi,

See This.

http://www.analyticsvidhya.com/blog/2014/12/remove-synthetic-key-concatenation-link-table-qlikview/

Re: syn keys

manojkulkarni — Mon, 14 Sep 2015 05:14:30 GMT

Rename ComapnyID in Item Table

Rename Volume in Transactions Table (if not being used) else u can rename it from Item table.

Concatenate WarehouseID & CompanyID to make a single Key field.

Customer & Branch as well you make a key to void synthetic keys.

Re: syn keys

mightyqlikers — Mon, 14 Sep 2015 05:52:39 GMT

hi,

Transactions:

load

Company_ID & Warehouse_ID & ITEM_ID & Volume & CUSTOMER_ID & BRANCH_ID as Key1

from transactiontable;

Dates:

load

from Datestable;

Company:

load

* from companytable;

Warehouse:

load

company_ID & Warehouse_ID as Key2

from

warehousetable;

items:

load

Company_ID & ITEM_ID as Key3

from Itemstable;

Customer:

load

Company_ID & Customer_ID as Key4

from CustomerTable;

Branch:

load

Branch_ID & Company_ID as Key5

from branch table;

link_Table:

load

distinct

Company_ID & Warehouse_ID & ITEM_ID & Volume & CUSTOMER_ID & BRANCH_ID as Key1,

Company_ID ,Warehouse_ID, ITEM_ID ,Volume ,CUSTOMER_ID ,BRANCH_ID

from transactiontable;

concatenate(link_Table)

load distinct

Company_ID from datestable;

concatenate(link_Table)

load distinct

Company_ID from companytable;

concatenate(link_Table)

load distinct

company_ID & Warehouse_ID as Key2,

company_ID ,Warehouse_ID from warehousetable;

concatenate(link_Table)

load distinct

Company_ID & ITEM_ID as Key3,

Company_ID ,ITEM_ID

from Itemstable;

concatenate(link_Table)

load distinct

Company_ID & Customer_ID as Key4,

Company_ID,Customer_ID

from customertable;

concatenate(link_Table)

load distinct

Branch_ID & Company_ID as Key5,

Branch_ID , Company_ID from branchtable;

Regards

$@M

Re: syn keys

sasiparupudi1 — Mon, 14 Sep 2015 06:29:35 GMT

Use Qualify statement to use fully qualified names on non key fields.

if CompanyID is the key filed that is joining all the tables then you could use

Qualify VOLUME,ITEM_ID,CUSTOMER_ID; before the load statements.

what this will do is to create fields in the tables as follows

customer.CUSTOMER_ID

items.ITEM_ID

hth

Sasi

Re: syn keys

hic — Mon, 14 Sep 2015 13:44:52 GMT

Yes, you need to remove them. I don't know your data model exactly, but I am fairly sure that "Volume" is not a key. So it should not be used to form a key.
For each link, you must sit down and think. "What is really the link here?" "Do I need all fields as links?" For instance, you have a table "Warehouse" that links to the "Transactions". Is WAREHOUSE_ID enough to specify the warehouse? Or do you need COMPANY_ID also? My guess is that WAREHOUSE_ID is enough, and then you should use WAREHOUSE_ID only.

HIC