Synthetic Key Removal?

[#1]

I am trying to remove a synthetic key but am struggling how to do so. I have looked at a few examples on the community but am having a hard time translating this to my data model.

//First two tables get combined. master_date combines all my dates together- to my calendar table. Sales_ID is a an identifier for each sales person.
OrderHeader:
LOAD
date(OrderDate) AS master_date,
SQL SELECT *
FROM OrderHeader;
SalesTerritory: //this table just joinsinto the OrderHeader table
LEFT JOIN (SalesOrderHeader) LOAD
text(BusinessEntityID) AS Sales_ID,
SQL SELECT *
FROM SalesTerritoryHistory;
Quota:
LOAD
BusinessEntity_ID AS Sales_ID,
date(master_date) AS master_date,
RESIDENT QuotaTemp;
DROP TABLE QuotaTemp;

This should be relatively straight forward but I seem to be missing something. It creates a key on master_date and sales_id.

Thanks.

[#2]

Well you've got Sales_ID and master_date in two different tables which is obviously the problem. That being said you need to concatenate a key like...

Sales_ID & '|' & master_date as 'Key'

I don't think I've ever seen date used as a key before but... I don't see any reason why you couldn't? I'm not exactly the go to guy for concatenating keys or anything but maybe that's a start. It's hard to say because you have SQL SELECT * in there twice and I have no idea what fields it's loading.

[#3]

Yes. I have tried this. This creates a 'unique key' just as it is supposed to. However it does not connect to the 'real keys'. I would like the SalesRep_ID (275, 276, etc..) to connect back to the 'real' SalesRep_ID and the same with the master_date (1/1/2007, etc). Currently it creates a unique key like this: 0|1/1/2007. Can I break down the key into its two parts and connect it back up within the OrderHeader and quota table? Or using a Resident Load?

if(SalesRep_ID='|',0,SalesRep_ID) &'|'& QuarterStart(master_date) AS mast_key //my attempt to break it apart
SalesRepID_ID & '|' & master_date as 'Key' //creates the '275|1/1/2007' key

[#4]

I think this is better advice. Try this:

<pre>
Quota:
LOAD
BusinessEntity_ID AS link_Sales_ID,
date(master_date) AS master_date,
RESIDENT QuotaTemp;


DROP TABLE QuotaTemp;

Then in the SalesOrder table, add another field called [link_Sales_ID] that is exactly the same as Sales_ID. You might need to do this on a resident load and drop the initial table or you could probably just add the field twice (once for the first table the table is loaded and the other for the join).

The only problem you might have is if there are Sales_ID's in the Quota table that are not in the SalesOrder table. If there are you probably need to make a link table by concatenating the two tables or something but... hopefully you don't need to do that because that's something I am terrible at. Hopefully this advice isn't terrible as I'm lucky and rarely need to do this.

[#5]

I have tried something similar. See below. I am new to the Resident Loads as well.

Concatenate (SalesOrderHeader)
LOAD
if(SalesRep_ID='|',0,SalesRep_ID) &'|'& QuarterStart(master_date) AS mast_key
//SalesRepID_ID & '|' & master_date as 'Key'
RESIDENT SalesOrderHeader;

Quota:
LOAD
//BusinessEntity_ID AS SalesRep_ID,
BusinessEntity_ID,
//date(master_date) AS master_date,
date(master_date) AS quota_date,
//BusinessEntity_ID & '|' & master_date as 'Key',
//AUTONUMBER(''&'-'& master_date &'-'& BusinessEntity_ID &'-'&'') AS MastKey,
if(BusinessEntity_ID='Website',0,BusinessEntity_ID) &'|'& QuarterStart(master_date) AS mast_key,
//(QBusinessEntity_ID & '|' & QuarterStart(master_date)) AS master_key,
Quota
RESIDENT QuotaTemp;

DROP TABLE QuotaTemp;

QuotaID:
LOAD
BusinessEntity_ID AS SalesRep_ID
RESIDENT Quota;

QuotaDate:
LOAD
quota_date AS master_date
RESIDENT Quota;

DROP TABLE Quota;

My question now is how do calculate the quota amount? If I put Quota on one of the tables it only calculates it by date or by id. How else I can calculate this?

[#6]

If you post a small .qvw that would help.

For example you have

<pre>
LEFT JOIN (SalesOrderHeader) LOAD

but I don't see a SalesOrderHeader table in the code you posted. Is the date field in EVERY table? I'm not even sure how many tables there are.

[#7]

Here is my schema with the key. This generates the correct results but I do not want the key to be created. My attempts to remove it have failed.

[#8]

Here is the section of my QVW. Thanks for all your help.

[#9]

Make the mast_key field in the QuoteFinal table as well and then do the following:

DROP FIELDS [master_date],[SalesRep_ID] FROM QuoteFinal;

If you made the mast_key right, the fact that salesrep_ID and master_date aren't in that table won't matter b/c they are linked in the SalesOrderHeader table. Hopefully that works b/c if not... I'm lost.

[#10]

Not sure if I set this up correctly. mast_key appears to be correct. It still concatenating the results 'correctly'. I did this but believe I am just running in circles.

Concatenate (SalesOrderHeader)
LOAD
if(SalesRep_ID='Website',0,SalesRep_ID) &'|'& QuarterStart(master_date) AS mast_key
//SalesRep_ID & '|' & master_date as 'Key'
RESIDENT SalesOrderHeader;

QuotaID:
LOAD
BusinessEntity_ID AS SalesRep_ID,
Quota AS QuotaID
RESIDENT Quota;

QuotaDate:
LOAD
quota_date AS master_date,
Quota AS QuotaDate
RESIDENT Quota;

QuotaFinal:
LOAD
BusinessEntity_ID AS SalesRep_ID,
quota_date AS master_date,
mast_key,
Quota
RESIDENT Quota;

DROP TABLE Quota;
DROP TABLE QuotaID;
DROP TABLE QuotaDate;
DROP FIELDS [master_date],[SalesRep_ID] FROM QuotaFinal;