Synthetic key and concatenate

[Reuven12]

Hey all,

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

Im 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 categoryId for the budget.

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

Thank you!!

[MayilVahanan]

Hi @Reuven12 

Might be, try like below

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

Budget:
Load
CategoryID &'-' & floor([Calendar date]) as Key
Amount,
Resident Budget;

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

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

[Reuven12]

Hey @MayilVahanan ,

In this case, the budget will be separate from the category table, no?

And also I want to keep the calendar date for the master calendar.

Thank you, Reuven.

[MayilVahanan]

Hi @Reuven12 

Budget is mapped with Actual. Actual is mapped with Product, Product will be map with Category.

[Reuven12]

Hey @MayilVahanan  you are right.

the problem is there was no actual sale in one specific date to one month (or there was a sale but not for the budget category), then I will not be able to see the budget.

Thanks!