0 Replies Latest reply: Jul 17, 2017 1:54 AM by Hung dep RSS

    Please help me to join tables

    Hung dep

      Hi all Qlikers,

      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....

      model.PNG

      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?

       

      Capture.PNG

       

       

      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:

      Left ‒ Qlik Sense

       

      Thank you and sorry if my English is not good. Anyone needs further explain please comment.