Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jim_chan
Specialist
Specialist

Can I create my composite key like this?

Hi guys,

i want to demonstrate how to remove synthetic key by using composite key. I have created a composite key.

But i am confused which table's common field should i comment? and how should i determine which table's common fields to be commented out?

I have commented common fields that available in my Sales Target table.

Orders:

LOAD

EmployeeID &'|' & Month(OrderDate) &'|' & Year as order_key,

OrderID,

     Month(OrderDate) as Month,

     OrderDate,

     EmployeeID,

     CustomerID,

     ProductID,

     APPLYMAP('Product_Details',ProductID,null()) as Product_Made,

     ShipperID,

     Year,

     Sales,

     Cost,

     Quantity

   

FROM

[Data Source\Order.xlsx]

(ooxml, embedded labels, table is Order);

Sales_Target:

LOAD

EmployeeID &'|' & Month &'|' & Year as order_key,

  //EmployeeID,

     //Year,

    // Month,

     Target

FROM

[Data Source\Sales_Target.xlsx]

(ooxml, embedded labels, table is Sheet1);

Rgds,

Jim

1 Solution

Accepted Solutions
awqvuserneo
Creator
Creator

Hi Jim,

I believe it doesn't matter much which fields that you comment it out, but in the above case, I'm leaning towards to comment out the Year, Month, and Employee ID in Orders table instead.The reasons are:

1. More likely than not, your Sales_Target already contains unique records based on EmpID, Year, Month combination, and I'd like to see the fields made up these unique combination from table viewer.

2. I prefer to treat the "Sales_Target" as my "Dimension" table, If you were to keep the 3 fields in Sales_Target (and commented them from the Orders instead), when you are displaying the EmpID, Year, and Month fields, it is as-if these fields come from Dimension table.

3. Orders table potentially have more records as it stores transaction records, thus keeping the table smaller by removing unnecessary fields will help speed up the reload process (even though in relatively few records, the speed improvement will be insignificant).

I'm sure others may have different opinions, and I'm totally welcome you to share yours. That's how we grow this community.


Cheers,

Anton

View solution in original post

6 Replies
awqvuserneo
Creator
Creator

Hi Jim,

I believe it doesn't matter much which fields that you comment it out, but in the above case, I'm leaning towards to comment out the Year, Month, and Employee ID in Orders table instead.The reasons are:

1. More likely than not, your Sales_Target already contains unique records based on EmpID, Year, Month combination, and I'd like to see the fields made up these unique combination from table viewer.

2. I prefer to treat the "Sales_Target" as my "Dimension" table, If you were to keep the 3 fields in Sales_Target (and commented them from the Orders instead), when you are displaying the EmpID, Year, and Month fields, it is as-if these fields come from Dimension table.

3. Orders table potentially have more records as it stores transaction records, thus keeping the table smaller by removing unnecessary fields will help speed up the reload process (even though in relatively few records, the speed improvement will be insignificant).

I'm sure others may have different opinions, and I'm totally welcome you to share yours. That's how we grow this community.


Cheers,

Anton

Digvijay_Singh

I think all the points mentioned by awqvuserneo are very critical, along with that you may think of connecting both the tables to a master calendar( using date key with all dates from order and target) and remove month and year from order and target tables, so this way your order and target tables are connected using EMP ID and both order and target table connected to master calendar using date field. I think going by your way may have confusion by treating target year/month same as order year/month and you may find difficult to answer questions like for which target year there is no sales for particular EMP ID if you comment out fields by treating them having same.

Anil_Babu_Samineni

Hope This helpful

Try With out Link table. And see how synthetic keys are there. After using composite key try to check Data Model

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
jim_chan
Specialist
Specialist
Author

do you mean.. try with or without linktable?

jim_chan
Specialist
Specialist
Author

I try not to use link table as i wanted to demonstrate removing synthetic key using composite key.

Digvijay_Singh

In this case the simplest and fastest solution is to concatenate target table to order table.