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

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

Fri, 05 Aug 2011 19:54:29 GMT

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

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

johnw — Fri, 05 Aug 2011 20:23:15 GMT

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,

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

Fri, 05 Aug 2011 20:29:31 GMT

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

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

Fri, 05 Aug 2011 20:36:25 GMT

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?

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

johnw — Fri, 05 Aug 2011 20:41:02 GMT

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.

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

Mon, 08 Aug 2011 17:14:34 GMT

Thank you for helping me out.

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

Thu, 08 Jan 2015 11:35:18 GMT

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