Data Model Help

[rittermd]

I'm sure that I am just not thinking straight.  But here is what I need to do.

I have a table that contains cases from Sales Force.  I have another table that contains all of the shipments processed.  Both for 2 years.

In this example there maybe 35,000 cases but there are 5M shipments.  Not all shipments have an issue to report.

I need to calculate by user who processed the shipment the percentage of those shipments that had a case.  So let's say they do 100 shipments and 10 generate a case.  Then their percentage of issues is 10%.

The problem that I am running into is that my data model associates the entry number on the shipment to the entry number on the case.  So I can't count the total number of shipments.  Only the shipments tied to the cases.  

If I make the shipments table an island not linked to the cases then I can't filter on user or anything else.

I am coming up empty on how to achieve this.  Help!!!

[anthonyj]

Hi,

If I understand correctly your question, you want to count the number of cases divided by the number of shipments with the association through the entry number. I'm assuming that the entry number is a 1:1 relationship between the two tables and are therefore unique in each table. 

When I need to perform a percentage in these circumstances I create a 1/0 flag in a new column in the table. This allows me to use sum, count and average (used to get the %) to get different distributions. To set this up in your example I would do the following:

CasesMap:

mapping

load distinct

  [Entry Number],

dual('Yes',1) as flag

from SalesForce;

Cases:

Load

  *

from SalesForce;

Shipments:

Load

  *,

applymap('CasesMap', [Entry Number], Dual('No',0))  as Issue

from Shipments;

This will result in a column on your shipments table called Issue that you can average (avg(Issue)) in a measure giving you the percentage, Sum; to get number of Cases or Count; to get the number of shipments. The added benefit of the dual function allows you to use Issue in a filter that will give you 'Yes' and 'No' instead of just 1's and 0's.

I hope I've understood your question correctly and have been of some assistance.

Regards

Anthony