Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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