Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jim_chan
Contributor III

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
Contributor

Re: Can I create my composite key like this?

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

6 Replies
awqvuserneo
Contributor

Re: Can I create my composite key like this?

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

Re: Can I create my composite key like this?

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.

Re: Can I create my composite key like this?

Hope This helpful

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

Life is so rich, and we need to respect to the life !!!
jim_chan
Contributor III

Re: Can I create my composite key like this?

do you mean.. try with or without linktable?

jim_chan
Contributor III

Re: Can I create my composite key like this?

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

Digvijay_Singh
Honored Contributor III

Re: Can I create my composite key like this?

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

Community Browser