Calculating a field in a table based on another table

[#1]

Hey All,

Please see my attached QVW.  In this file I have two fact tables - Invoices and Goals.  There is one dimension table - Products.  These are joined using the Lookup table.

During load I want to calculate the number of units of a goal that has been completed.  This is calculated as the number of units on invoices for a specific product.  I want to store this as a new field called Goals.InvoiceUnits.

My final goals table in the QVW would look like this (bold is the new field):

Goal.ID	Product.Code	Product.Name	Goal.Units	Goal.InvoiceUnits
G1	A	Pint Glass	10	3
G2	B	Silverware	4	3
G3	C	Notebook	3	0
G5	E	Oatmeal	11	0

Thanks!

Ken

[Anonymous]

Ken

Why not flatten your data into a single table and whack in a simple pivot table to show your required data

Best Regards,    Bill

[#3]

Hey Bill,

In my example file this might make sense.  However the document I'm creating is much more complex - I have about 10 fact tables and 10 dimensions.  Flattening the data is not an option.

Thanks.

[#4]

If it helps I would like to do something like this (psuedocode):

FOR EACH Goal:

     FOR EACH Invoice WHERE

                    SubField(Invoice.Key,'|',2) = SubField(Goal.Key,'|',2):

          ASSIGN Goal.InvoiceUnits += Invoice.Units;

     END.

END.

[#5]

Another problem with this is that a single invoice line could fufill multiple goals.  This case is not represented in my example.