I think I would combine Sales Table and Cust Rep Table into one table and add a descriptor field to the resulting table that will have two values: 'Sales' and 'Assignments'.
The fields Sales and Units in the resulting table will be empty for when the new descriptor field value is 'Assignments' and populated for the value is 'Sales'
Just a thought,
I am assuming from the description that the Combination of customer and reps on the sales table can and will be different to the combinations on the Cust rep table.
In this case I would alias the J_CUST_ID as Cust_With_rep. This would get rid of the synthetic key, but you will still see the Customers under the Rep, if needed you can then map any other details from the customer table to the Customer table, making sure to alias as well.
How about doing Mapping Loads with your Customer & Rep tables, followed by ApplyMap() 's to load the Customer & Rep Names into the Sales Table ?
This should result in an efficient single table data model.
Have a look at this Blog Post by HIC Don't join - use Applymap instead
Thank you all for your responses. By reading all of your responses,i understood how to design datamodel in qlik to achieve my requirement.
However i design my data model,in the end in User Interface users would like to see only Customer, Rep, Sales & Units.
scenario 1.User would like to see Rep and associated customers regardless of sales
scenario 2.User would like to see Rep,customers, sales & units
Since qlik association model is so good, based your responses concatenating cust rep table and sales table i.e loading all customers from cust rep into sales table & by removing duplicate customers works.
but i am having issues removing duplicates.below is my code
FROM [lib://Qlik_Practice/Qlik Practice1\Sales Table.xlsx]
(ooxml, embedded labels, table is Sheet1);
FROM [lib://Qlik_Practice/Qlik Practice1\Cust Rep.xlsx]
(ooxml, embedded labels, table is Sheet1)
where NOT EXISTS(J_CUST_ID,J_REP_ID);