Linking Tables

[#1]

Hi

I want to link tables , where the values are are in columns with different names.   Let me elaborate the issue im sitting with:

I have a Item, BPHDMI,  which is made up of 3 components as shown in table below callled Bill of Materials.  One of each of the component is needed to make BPHDMI.

Bill of Materials

Item Code	Component Code	Component Qty
BPHDMI	ZZBPHDMI	1
BPHDMI	ES	1
BPHDMI	ICHDMI	1

In the next table below , I have the cost of each component as well as the cost of the final product.

In this table , the component as well as the final product is under the column "Item Code".  Whereas above the Components are Item are in seperate columns.

Warehouse

Item Code	Unit cost
BPHDMI	500.00
ZZBPHDMI	300.00
ES	150.00
ICHDMI	50.00

What i am trying to create is a straight table or pivot table showing sum the costs of  components to be the total value of the final product.  ( as below) .

Item Code	Component Code	Unit Cost
BPHDMI	ZZBPHDMI	300.00
	ES	150.00
	ICHDMI	50.00
Total		500.00

How do i do this.  Please help.  Thanks.

I have attached an excel spreadsheet.  I also included a second item.

kind regards

Nayan

[#2]

Hi Nayan,

So build your link as

Bill.ComponentCode     as %C  (%C means a key, just call it as you want

WareHouse.ItemCode    as %C

will link both tables togethers on %C

in your chart still use Bill.ItemCode and Bill.ComponentCode as dim

and Warehouse.cost as expr

Note : an other solution is to concatenate both tables

best regards

Chris

[#3]

Hi Chris

Thanks for replying.  The changes to the scripting I've done , did not work.  See below.  Where am i going wrong?

[Bill of Materials]:

LOAD [Item Code] ,
[Component Code] as BOM,
[Component Qty] as BOM
FROM
[Qlikview Community - Items and Components.xls]
(biff, embedded labels, header is 1 lines, table is [Sheet1$]);

Warehosue:
LOAD [Item Code] as BOM,
[Unit cost]
FROM
[Qlikview Community - Items and Components.xls]
(biff, embedded labels, header is 1 lines, table is [Sheet1$]);

[#4]

Hi  Chris

Sorry, The above scripting for Bill of Materials should show [Item Code] as BOM and not [Component Qty] as BOM.

kind regards

Nayan

[#5]

Nayan,

try this

[Bill of Materials]:

LOAD

[Item Code]               as Bill.ItemCode,
[Component Code] as BOM,
[Component Code]     as Bill.COmponentCode,

[Component Qty]     as Bill.Qty
FROM
[Qlikview Community - Items and Components.xls]
(biff, embedded labels, header is 1 lines, table is [Sheet1$]);

Warehosue:
LOAD

[Item Code] as BOM,
[Item Code]      as DWH.ItemCode,

Unit cost]     as DWH.UnitCost
FROM
[Qlikview Community - Items and Components.xls]
(biff, embedded labels, header is 1 lines, table is [Sheet1$]);

Chris

[#6]

Hi.

Will try and let you know.

kind regards

Nayan

[preminqlik]

THIS SHOULD WORK

[#8]

Hi Chris

Thanks for your reply.  However it does not work.  I have attached the QV Model and Excel File.  The components do not link up with the unit costs.

kind regards

Nayan

[#9 · Accepted solution]

Hi Nayan

Here it is

best regards

Chris

[#10]

Hi Chris

Thank you for all your help.  I appreciate it. It works.

kind regards

Nayan