Link Table + Generic Keys + ?

[drewwilliams]

Hello All,

I've had a problem linking my data together in a way that allows for all attributes to be used to filter across all facts and dimensions. I posted this question with sample data here: Join, Link, Concat, ApplyMap, or all of the above? but I have attached another set of example data to be linked.

I am trying to connect BoM data with inventory and sales data. So when a product is selected, I want to have it linked so that I can see the inventory and sales for the selected product, as well as the inventory of the parts that go into that product.

I have looked at many documents and discussions regarding a link table and that gave me a partial connection but I have yet to connect all the data for the desired result. I've explored Link tables and concatenation of fact tables but still not getting the correct result.

I would really appreciate some help fully connecting this data.

I've attached a new set of sample data. Please see this and the discussion linked above to view past issues when attempting to connect this data.

Thank you!!

[robert99]

Hi

I read this on your comment on Henrics blog post

"My goal is to link BoM data to Inventory and Sales data. I've also read your brief on creating a BoM but I won't be applying that detail to my model quite yet."

I have done this for the finished product manufacturing units. But only by setting up a BoM as per Henric's blog post on this subject. I can now link the finished product manufactured  (or sales finished product sales) quantities with the RM and packaging item usage for thee items manufactured (sales) 

[drewwilliams]

Hi Robert,

Thanks for the reply. Can you provide an example of how you did this?

When you did this, are you able to see the inventory for both the finished product and its components when an finished product is selected and can you also see the sales for the selected finished product?

[robert99]

Hi

I just followed Henric's blog on BoM. Using the script below

I did it for a small business client so that I could input the manufactured details (Excel) and produce an Excel sheet for uploading into the accounting/ stock control system showing the manufactured items at full cost based on the BOM's (purchases upload), the offsetting purchases credit note and raw material usage (Zero value Sales invoice)

It produce a report like this with cost details for £, US and Euro based on one unit of finished product. It automatically updates using the latest XRs . This is for one finished product that could be used to find the cost for quantity sold (I use it to find the cost for manufactured finished product

BoM:
Load *,
   Quantity as TotalQty,
   Subfield('/' & Path,'/',Level) as ParentLineNo;
Load *,
   If(Level>1,Left(Peek(Path),Index(Peek(Path)&'/','/',Level-1)-1)&'/')&LineNo as Path
   FROM BoM ;

MaxLevel:
Load Max(Level) as MaxLevel Resident BoM;
Let vMaxLevel = Peek('MaxLevel',-1,'MaxLevel');
Drop Table MaxLevel;

For vLevel = 1 to vMaxLevel -1
   Left Join (BoM) Load
      LineNo as ParentLineNo,
      TotalQty as Multiplicity
      Resident BoM Where Level=$(vLevel) ;
   NewBoM:
   Load *,
      TotalQty*If(IsNum(Multiplicity),Multiplicity,1) as NewQuantity
      Resident BoM;
   Drop Field Multiplicity, TotalQty;
   Rename Field NewQuantity to TotalQty;
   Drop Table BoM;
   Rename Table NewBoM to BoM;
Next vLevel