topic Re: Calculating a field in a table based on another table in QlikView

Calculating a field in a table based on another table

Thu, 14 Nov 2013 14:46:49 GMT

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

Re: Calculating a field in a table based on another table

Anonymous — Thu, 14 Nov 2013 15:05:27 GMT

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

Re: Calculating a field in a table based on another table

Thu, 14 Nov 2013 15:08:18 GMT

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.

Re: Calculating a field in a table based on another table

Thu, 14 Nov 2013 15:10:25 GMT

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.

Re: Calculating a field in a table based on another table

Thu, 14 Nov 2013 15:12:02 GMT

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