How to create a new field using fields from different tables?

[#1]

Good afternoon respected members,

I am new to qlikview and BI in general. So any help would be appreciated.

I would like to create a new field called 'Costs'. In order to create this field i would need to use two different fields from two different tables. Costs = Product Standard Cost * Order Quantity. The fields involved in this metric are OrderQty and StandardCost. OrderQty can be found under the Sales tab in the second LOAD script and StandardCost can be found under the Product tab in the Product table.

I am attaching a copy of the document. Looking forward for the communities help in this regard.

Thanks,

Ammad

[johnw · Accepted solution]

In a chart, you'd just use StandardCost * OrderQty.  It doesn't matter that they're on different tables as long as the data model has the correct connections between the tables, and they look good to me on the surface.

Did you want to calculate Costs in the script instead of in charts?  There are several ways.  What I might do is load in a mapping table of the standard costs by product:

[Product Costs]: MAPPING LOAD ProductID, StandardCost RESIDENT Product;

And then when joining the sales order detail onto the header, add another field:

OrderQty * applymap('Product Costs',ProductID) as Costs,

[#3]

So you have OrderQty from SalesHeader Table and StandardCost from Product Table.

So, here is a question for you, in which table do you want this new field "Costs". Do you want it in Sales Header Table or Product table. You would need to mention it.

Say, you want it in Product table,

Then write in your script the following lines

Temp1:

Load

ProductID as ProdId,

StandardCost as StdCost

resident Product;

Outer Join(Temp1)

Load

ProductID as ProdId,

OrderQty as OrderQuanity

resident SalesHeader_New;

left Join(Product)

Load

ProdId as ProductID,

OrderQuanity * StdCost as Costs

resident Temp1;

drop table Temp1;

Hope this helps,

do letme know.

Thanks,

Bikash Debnath

[#4]

i read that its best to have one fact table,  resulting in a star schema. Based on this, i would like my sales table to be the fact table and store all facts in there. I beleive Costs would be a fact, correct?

[johnw]

Correct.  Individual sales are more fundamental that products.  Product should here be considered a dimension of sales.  Costs are associated with the sales, not with the product.  So costs go on the sales table.

[#6]

Thank you for helping me out.

[#7]

Excellent Explanation John ...........