Below is my data model and let me explain my situation:
I get Trade program outlet-list (Table 1), which includes list of the outlets of certain programs. DiscusKey means the combination of Distributor and Customer.
Distributor sales-out (Table 2) is somehow like Sales order report by outlet (Customer). Distributor ID in this table links to the distributor structure (Table 3) to generate data by Area/Region/State....
The Table 1 includes 100 customers, for example, but not all of those purchased any product in one month, for example, let's say 90 of those 100 are in Table 2.
Then if i summarize sales by Trade program by Area/Region..., it only contains 90 customers appearing in table 2. This is fair.
The problem is: How to join table 1 and table 2 to answer questions like:
How many outlets joined a certain trade program by Area/Region?
How many of them purchased?
The outlets which did not purchased (Total Sales = 0) does not belong to any Area/Region/Distributor .. with this way of linking Distributor structure. (I must link this way, Distributor Structure should not be linked directly to Table 1)
I attached the QS app so that you guys can help me in the script. I also read this article but can not solve: