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 key and concatenate

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?

maayanreu12_0-1606211105664.png

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!!

4 Replies
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

 

Thanks & Regards,
Mayil Vahanan R
Reuven12
Contributor II
Contributor II
Author

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.

Thanks & Regards,
Mayil Vahanan R
Reuven12
Contributor II
Contributor II
Author

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!