Avoiding a synthetic key

[hobanwashburne]

Looking for some help with my data model. The attachment contains sample sales data for five customers that is distributed across 4 salesman and three divisions. My current application is similar in structure to the one that is attached.

Now I wish to add meeting data containing Dates & Customer Numbers (also contained in the SalesData table) as well as the salesman (contained in the Sales Allocation table). It is also possible for a salesman to visit a customer to which he is not allocated.

I would prefer not to join all of the tables together as it functions as a cross-check to make sure that all of the allocations sum to 100%.

How can I structure these without creating a synthetic key?

[Josh_Good]

Hi Scott,

What I would recommend is you load the rep table twice because you have it acting as two different things.  The allocations to each rep and the meetings reps attended.  This will enable you to answer more questions about the data (e.g. sales from with a customer that a specific rep attended a meeting, or sales by reps who was allocated to an account).

I would recommend against not associating all the tables.  Doing so will limit your ability to ask questions of the data.  If you do will not affect your ability to ensure the allocations sum correctly because Qlik uses an associative approach, not a query based approach.

please see attached.

Josh

Qlik

[hobanwashburne]

I agree with you that all of the tables should be associated, but the problem is with how the user is selecting the data. There needs to be a single selection box for sales reps and one for the customer.

If the user selects the customer they should be able to see the associated sales data as well as which salesmen have visited regardless of whether that salesman is associated with that customer. Likewise, if a salesman is selected all of the sales data for the customers that he is associated to in addition to all of the meetings he has attended should be displayed.

I can get the results I want (attached) if I join the allocation table to the sales data and perform the allocation in the script:

(net * (SalesAllocation/100) as net

Then I concatenate the meeting data to the sales data

However, I would like to find an alternative to joining everything into one table

please see attached

[Josh_Good]

Hi Scott,

Unless I am misunderstanding you, I believe you can do everything you describe with the example I attached above.

If you select on a customer you can see the allocation assigned to that customer, which rep is assigned the allocation and all sales reps who attended a meeting with the customer.

Similarly, if you select on a sales rep (the field I called SalesAllocationSalesRepName) then you can see the allocation for that sales rep by the customer and all of the meeting the sales rep attended (along with who else attended).  Note if the sales rep didn't attend a meeting that they had an allocation for then you would be able to see that, you could even have the highlighted if it was important.

I hope this helps clarify what I did.

Josh

Qlik

[hobanwashburne]

Josh,

You are very, very close. However, looking at your second screen shot where "Sally" is selected the chart second chart on the right also shows information for Bob (because he attended a meeting with Sally). This will be confusing to the user who was only expecting to see information about Sally.

Likewise, if you select Bob, the meeting he attended with Sally to customer A0013 does not show up because he is not associated with customer A0013. Bob needs to get credit for attending the meeting even though it wasn't one of his customers.

[Josh_Good]

‌Hi Scott,

I would encourage you to come consider showing your users "the whole truth". Bob did attend a meetinng with Sally. This information could be very useful to Sally so completely hidding this may result in less value from the app.

That said I do see your point that users also want to see just information on themselve to do this I would use set analysis on the expressions regarding meetings.  The expression would be something like

count ({$<AllocatedRepName = , MeetingRepName = {$(=Chr(39) & Concat(Distinct AllocatedRepName, Chr(39) & ',' & Chr(39)) & Chr(39))}>} MeetingID)}

Appologies I'm writing this on my phone so I couldn't check the expression but this should put you on the right track.

-Josh

Qlik