Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Customer Number | Customer Name |
1 | Cust1 |
2 | Cust2 |
3 | Cust3 |
4 | Cust4 |
5 | Cust5 |
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 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 |
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.
sum({$<DateType={'Create'}>} Created)
sum({$<DateType={'Resolved'}>} Resolved)
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 🤔
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?
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.