Canonical Dates over multiple associated tables without a common key

[JA5]

Hello everyone, i'm having issues trying to create a canonical date in qlik sense when associating tables and was wondering if anyone would be able to help out. In basic terms i have 2 tables: "orders" and "sales rep visits", which are associated to the "customer data" table by customer number, (very simplified) script below:

[Customers]:

LOAD

[Customer Number],

[Customer Name]

 FROM [Customers.qvx]

(qvx);

[Orders]:

LOAD

[Order Customer Number] as [Customer Number],

[Item],

[Quantity],

[Order Date],

 FROM [Orders.qvx]

(qvx);

[Visits]:

LOAD

[Visit Customer Number] as [Customer Number],

[Visit ID],

[Visit Date]

 FROM [Visits.qvx]

(qvx);

Given the order data and visit data are completely separate datasets with customer number being the only common field between them, i don't think the usual way i do canonical dates with a common key in each table will work, and they need to be associated as opposed to joined due to the '1 to many' relationships that are formed from combining order and visit data. Would anyone know how i could create a canonical date between the 2 date fields (order date and meeting date) even without a common key linking them?

Any help would be appreciated! Thanks!

Tables below for added context:

Customer Table 

Customer Number	Customer Name
1	Cust1
2	Cust2
3	Cust3
4	Cust4
5	Cust5

Order Table

Order Customer Number	Item	Quantity	Order Date
3	Apple	4	2024-02-16
4	Pear	8	2024-02-18
4	Grape	10	2024-04-01
5	Strawberry	1	2024-03-19
2	Orange	3	2024-01-26
1	Apple	5	2024-01-02
1	Orange	5	2024-03-23
1	Apple	2	2024-04-17

Visit Table

Visit Customer Number	Visit ID	Visit Date
3	abc	2024-01-21
1	def	2024-01-26
2	ghi	2024-02-02
4	jkl	2024-02-28
2	mno	2024-03-13
3	pqr	2024-03-17

[carlcimino]

your bridge date table (created by resident loads from the fact table) should have a date type differentiator that gets used in the set analysis.  give HIC's blog post a couple read throughs.

Canonical Date 

sum({$<DateType={'Create'}>} Created)

sum({$<DateType={'Resolved'}>} Resolved)

[JA5]

Hi carlcimino thanks for your reply, I’ve seen HIC’s canonical date post and have used that method in the past with success. The issue I’m having with this one is there isn’t a common key (or in HIC’s words, there isn’t a table with a grain fine enough) between the “orders” and “visits” tables, as HIC states usually I’d have an order line ID with distinct dates or I have had success with using RowNo() as the common key when I have multiple dates in one table (struggling with getting a distinct RowNo() for each possible permutation when I have 2+ associated tables), but I have no such distinct keys on this one. Unless I’ve completely misunderstood his canonical date logic 🤔

[carlcimino]

Sorry I probably read your post to quickly.  It does sound like the part of HIC's blog post where he says have multiple Master Calendars for each of the individual dates and then bridged to a canonical calendar?  Otherwise is it possible to concatenate your fact tables with a field to identify invoice, orders, etc so the RowNo() is coming from a single table?  

[marcus_sommer]

What do you want to get isn't possible with this kind of data-structure. The essential part of information didn't exists.

But you may create some data to relate the period-information to each other. One way might be to load the visits ordered by customer and date (desc) and to create a FROM - TO interval of the times between the visits with the interrecord-functions of peek() and previous(). The next step might be  to apply an intervalmatch or an internal while-loop to resolve the intervals into dedicated dates. This date + the customer are then a valid key to join/map the data or to associate the tables.

Surely not trivial but based on this it should be possible to display the chain of events how many visits before and after a sale were there and also the date-offset between sale and visit.