Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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$]);
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
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
Hi.
Will try and let you know.
kind regards
Nayan
THIS SHOULD WORK
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
Hi Nayan
Here it is
best regards
Chris
Hi Chris
Thank you for all your help. I appreciate it. It works.
kind regards
Nayan