Synthetic keys for different tables issue

[Reuven12]

Hey all,

I need some help,

I have 2 tables: one for budget and one for actual. 

I'm doing concatenate between them but the issue is that the actual table contains productID while the budget table contains only the category table.

Product and category are different tables but even if ill make join between them I will still need the category Id for the budget.

Also, I can't lose my calendar date because I need it for the master calendar.

How can I avoid the synthetic key issue and not create a circular reference as well?

Actual:
Load
[Field type],
CustomerID,
[Calendar date],
OrderID,
ProductID,
Quantity,
Amount,
CANCEL_FLAG
//CategoryID as CategoryIDIndex,
// CategoryID &'-' & ProductID as [CategoryProductIndex]
Resident Sales;

Load
'Budget' as [Field type],
CategoryID ,
Amount,
[Calendar date]
Resident Budget;

Products:
LOAD
CategoryID,
ProductID,
ProductName as [Product name],
SupplierID,
"TYPE" as [Product type]
Products

Categories:
LOAD
"Category Desc",
CategoryID,
CategoryName
Categories

Master calendar: 

[Calendar date],

[Week],

[Year]

...

What can I do?

[ArnadoSandoval]

Hi @Reuven12 

You need to alias the CategoryID column in the Budget table, with something like this:

Load
'Budget' as [Field type],
CategoryID    As BudgetCategoryID,
Amount,
[Calendar date]
Resident Budget;

Your Category should have two ID columns as shown:

Categories:
LOAD
   "Category Desc",
   CategoryID,
   CategoryID    As BudgetCategoryID,
   CategoryName
Resident Categories

The Categories tables has two ID columns, the first one service the Products assosiation, the second, BudgetCategoryID associates with the Budgets table

HTH