topic Re: Linking Tables in QlikView

Linking Tables

Wed, 23 Oct 2013 13:28:15 GMT

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

Re: Linking Tables

Wed, 23 Oct 2013 13:44:38 GMT

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

Re: Linking Tables

Wed, 23 Oct 2013 13:55:42 GMT

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$]);

Re: Linking Tables

Wed, 23 Oct 2013 14:08:18 GMT

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

Re: Linking Tables

Wed, 23 Oct 2013 14:25:20 GMT

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

Re: Linking Tables

Wed, 23 Oct 2013 15:45:45 GMT

Hi.

Will try and let you know.

kind regards

Nayan

Re: Linking Tables

preminqlik — Wed, 23 Oct 2013 16:21:07 GMT

THIS SHOULD WORK

Re: Re: Linking Tables

Thu, 24 Oct 2013 05:48:15 GMT

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

Re: Re: Re: Linking Tables

Thu, 24 Oct 2013 08:35:52 GMT

Hi Nayan

Here it is

best regards

Chris

Re: Linking Tables

Thu, 24 Oct 2013 09:37:33 GMT

Hi Chris

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

kind regards

Nayan