Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Reuven12
Contributor II
Contributor II

Synthetic keys for different tables issue

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?

 
 
 

111111.JPG

 

 

 

 

 

1 Reply
ArnadoSandoval
Specialist II
Specialist II

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

 

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.