Circular Reference Problem - Link Table Or...?

[qlikdan]

Hi Community,

I have what I think is a somewhat unique problem that I could use some advice on. I need to develop an app for a sales system where the sales process has three steps: Order, Activation, and Registration. The unit is Ordered, then the unit is Activated, then the unit is Registered. At each step there is an "Agent" (the user who completes the step) and a "SKU" (the ID of the unit being affected during the step).

The unique part is that any of the three steps can be skipped. So not every Activation record will tie to an Order record and not every Registration will tie to an Activation record. This is a fact of the sales process flow where exceptions and replacement units are sold without necessarily entering an Order or Activation into the database. Further, there can be a time delay of weeks or months between each step. So the data tables will look like:

Normally this would be no problem and I would just remove the AgencyID and SKUID from Activations and Registrations, but since I can't rely on everything to have a record in Orders, I need to call up these IDs in each table. Likewise, I can't simply rename the columns because I do need the finished app to allow for seamless filtering by Agent and SKU. But the current scheme would certainly result in a circular loop as Qlik Sense tried to join Agencies and SKU onto each fact table. 

Is there some kind of link table I need to build here, or some other solution I am missing?

[lironbaram]

hi 

it's seems you have two options :

1. create a link table the host the common fields between the three tables , 

in your case create a key with AgencyID & '_' & SKUID  in each table , and than load from each table 

the distinct records from the three tables of the fields containing the key fields 

concatenate all three tables toghether 

something like :

KeyTable:

load distinct 

           AgencyID & '_' & SKUID,

         AgencyID,

         SKUID

resident Orders;

concatenate (KeyTable)

load distinct 

           AgencyID & '_' & SKUID,

         AgencyID,

         SKUID

resident Activations;

concatenate (KeyTable)

load distinct 

           AgencyID & '_' & SKUID,

         AgencyID,

         SKUID

resident Registrations;

or 

2. join all table together during your load script