Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Community
under the following url https://community.qlik.com/docs/DOC-5834 Rob Wunderlich makes usefull suggestions regarding how to link two or more dates from different fact tables.
My question is:
In the Rob's example it is all about facts connected with OrderID which defines each fact date in one line. What if there are two fact tables (Purchasing and Sales) having their own OrderIDs (PurchaseOrderID and SalesOrderID)?
How in this case to connect these 2 fact tables and how to build a common calendar (as Rob does)?
DETAILS
I’ve created an app to reflect 2 scenarios (purchasing and sales) of AdventureWorks(2017) database. For this purpose a common calendar for both fact tables is needed. For example, in order to put Total Sales (from Sales) and Total costs (from Purchasing) on the same time line.
The Problem is:
For this reason there is no field defining Purchasing and Sales at once. How to build a common calendar for both Facts as Rob suggests?
Kind regards
Denis
I would concatenate these two into a single fact table. Alias the PurchaseOrderID and SalesOrderID to a common field OrderID, The same with the date fields and any other fields that are named differently but contain similar information. Add a derived TransactionType field set to 'Purchase' or 'Sales' to identify the type of transaction (which you can use in set expressions later in the front end).
Now if you have more than one date field (eg OrderDate, CompletedDate) you can use the canonical date propiosed by Rob, or here:Canonical Date
Hi
Can't you link first fact table with purchase date and second one with sales date?
Regards
This is exactly the problem: the only link between Purchase and Sales is Product, but there are products which never get bought from vendors (bikes) and products which never get sold to customers (components)
I would concatenate these two into a single fact table. Alias the PurchaseOrderID and SalesOrderID to a common field OrderID, The same with the date fields and any other fields that are named differently but contain similar information. Add a derived TransactionType field set to 'Purchase' or 'Sales' to identify the type of transaction (which you can use in set expressions later in the front end).
Now if you have more than one date field (eg OrderDate, CompletedDate) you can use the canonical date propiosed by Rob, or here:Canonical Date
OK but you can still make a link with a common calendar table. I don't understand what is your problem in fact
Could you give an example?
Concatenation of two fact tables (Sales and Purchase) has helped, so that the common calendar (based on Commondate, which is the result of OrderDate and PurchaseDate) could be created and works fine.
Thank you jontydkpi