Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Linking Tables

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 CodeComponent CodeComponent Qty
BPHDMIZZBPHDMI1
BPHDMIES1
BPHDMIICHDMI1

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 CodeUnit cost
BPHDMI500.00
ZZBPHDMI300.00
ES150.00
ICHDMI50.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 CodeComponent CodeUnit Cost
BPHDMIZZBPHDMI300.00
ES150.00
ICHDMI50.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

1 Solution

Accepted Solutions
Not applicable

Re: Re: Re: Linking Tables

Hi Nayan

Here it is

best regards

Chris

9 Replies
Not applicable

Re: Linking Tables

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

Not applicable

Re: Linking Tables

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

Not applicable

Re: Linking Tables

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


Not applicable

Re: Linking Tables

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

Not applicable

Re: Linking Tables

Hi.

Will try and let you know.

kind regards

Nayan

preminqlik
Valued Contributor II

Re: Linking Tables

THIS SHOULD WORK

Not applicable

Re: Re: Linking Tables

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

Not applicable

Re: Re: Re: Linking Tables

Hi Nayan

Here it is

best regards

Chris

Not applicable

Re: Linking Tables


Hi Chris

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

kind regards

Nayan

Community Browser