Qlik Sense Model: Best Way to Link Purchase Orders ↔ Receipts ↔ Supplier Invoices (Many‑to‑Many)
Hi all,
I’m looking for advice on how to model a classic order → receipt → invoice flow in Qlik Sense without breaking the relationships or creating circular references.
Produce a single report that lists order‑line details (part, ordered qty, order value) alongside the related supplier invoice lines (invoiced qty, invoice amount, VAT, etc.). Key complicating factors:
Many‑to‑many
One order line can be covered by multiple invoices.
One invoice line can be budgeted against multiple orders.
Assoc link is via Receipts, not directly Order ↔ Invoice.
Each fact table must stay at line‑level grain.
Design dilemma
My instinct is to concatenate the three fact tables into one fact (with a “source” flag) and resolve relationships with a link key, but I’m worried that concatenation will sever the two‑hop Order → Receipt → Invoice chain or cause synthetic/circular keys.
Questions to the community
Would you concatenate these three facts, or keep them separate and create a bridge (/link) table?
If concatenating, how would you build the composite key so that both hops (Order→Receipt, Invoice→Receipt) still work?
Any best‑practice examples (scripts or data models) for handling this pattern in Qlik Sense?
These are very good questions, and there is no single "correct answer" for them - the preferred solution depends on many factors, including the types of analytics that are required, and on data volumes, and on other factors.
I can offer some thoughts for your consideration:
1. Between Concatenated Fact data models and Linked Tables data models, I'd typically prefer Linked Tables for smaller data volumes, and I'd gravitate toward Concatenated facts for larger data models, for performance reasons. However, once again, the specific makeup of your data may change this preference either way.
2. I'd probably opt to replace the link table that connects Orders - Receipts and Receipts-Invoices separately with a denormalized data structure, in which all three data entities are associated directly.
3. The many-to-many relation between Orders and Invoices is a tough problem. I'd probably split every Order line into several lines, based on the corresponding receipts and invoices, and vice versa - split every invoice line into several lines based on the corresponding Order lines. For example, if 100 units were ordered, and they got received in 3 portions - 20, 30, and 50, I'd split the original order line into 3 lines with the same Line number and with Ordered quantities being 20, 30, and 50.
4. In the original data sources, I assume that Orders, Receipts and Invoices have Lines and Headers. If that's the case, I'd probably keep a single concatenated fact, made up from denormalized Lines (see #2 and #3 above), and link them to the separate Headers tables for Orders, Receipts, and Invoices.
See if these ideas help you develop your solution.
In addition, allow me to invite you to my Qlik Expert Class that I'll be teaching in Vienna, Austria on September 22-24. I will be teaching advanced data modeling, covering some of these challenges, along with advanced scripting, performance optimization, and advanced aggregation and Set Analysis techniques. You will learn the most advanced Qlik methodologies that will help you solve tough problems like this one.
These are very good questions, and there is no single "correct answer" for them - the preferred solution depends on many factors, including the types of analytics that are required, and on data volumes, and on other factors.
I can offer some thoughts for your consideration:
1. Between Concatenated Fact data models and Linked Tables data models, I'd typically prefer Linked Tables for smaller data volumes, and I'd gravitate toward Concatenated facts for larger data models, for performance reasons. However, once again, the specific makeup of your data may change this preference either way.
2. I'd probably opt to replace the link table that connects Orders - Receipts and Receipts-Invoices separately with a denormalized data structure, in which all three data entities are associated directly.
3. The many-to-many relation between Orders and Invoices is a tough problem. I'd probably split every Order line into several lines, based on the corresponding receipts and invoices, and vice versa - split every invoice line into several lines based on the corresponding Order lines. For example, if 100 units were ordered, and they got received in 3 portions - 20, 30, and 50, I'd split the original order line into 3 lines with the same Line number and with Ordered quantities being 20, 30, and 50.
4. In the original data sources, I assume that Orders, Receipts and Invoices have Lines and Headers. If that's the case, I'd probably keep a single concatenated fact, made up from denormalized Lines (see #2 and #3 above), and link them to the separate Headers tables for Orders, Receipts, and Invoices.
See if these ideas help you develop your solution.
In addition, allow me to invite you to my Qlik Expert Class that I'll be teaching in Vienna, Austria on September 22-24. I will be teaching advanced data modeling, covering some of these challenges, along with advanced scripting, performance optimization, and advanced aggregation and Set Analysis techniques. You will learn the most advanced Qlik methodologies that will help you solve tough problems like this one.
Thanks a lot for the detailed feedback—especially the section on breaking the many-to-many by “exploding” the lines (your step 3). That idea really clicked for me. My plan is to keep the lean LINK-hub model as the primary Qlik data model , and then generate a derived extract where order and invoice lines are pre-allocated against their receipt splits. That flat table will be perfect for finance users who need a single exportable sheet in Excel.
