Bill of Materials (Joining)

[#1]

Greetings All,

I'm having a problem getting some data out of an ERP System.

I've got Three Tables (Shown in Code Below, Can't paste in code block, site jumbles it)

BILLJOB:
LOAD "Component_Job",
"Last_Updated",
"Manual_Link",
"Parent_Job",
"Relationship_Qty",
"Relationship_Type",
"Root_Job";
SQL SELECT "Component_Job",
"Last_Updated",
"Manual_Link",
"Parent_Job",
"Relationship_Qty",
"Relationship_Type",
"Root_Job"
FROM PRODUCTION.dbo."Bill_Of_Jobs";


JOBS:
LOAD Job,
"Part_Number",
Rev,
Status,
"Top_Lvl_Job" as Root_Job;
SQL SELECT Job,
"Part_Number",
Rev,
Status,
"Top_Lvl_Job"
FROM PRODUCTION.dbo.Job WHERE Status Like '%Temp%';


MATLREQ:
LOAD Job,
Material,
"Material_Req",
"Material_ReqKey",
"Quantity_Per";
SQL SELECT Job,
Material,
"Material_Req",
"Material_ReqKey",
"Quantity_Per"
FROM PRODUCTION.dbo."Material_Req";

If you've worked with ERP systems, you can probably imagine the problem im running into.

I've got a Bill of Jobs which has a Parent and Child Job for every part in as assembly

I've got a Jobs Table which can be used to reference each part for Parent and Child

For Each Part Number there are Materials used from the MATLREQ table..

Essentially taking the Bill of Jobs and Turning it into a Bill of Materials (Replacing the Jobs with Their Parts, Components, and Materials)

Example:

BILLJOBS

ParentJobA , ChildJobA

ParentJobA, ChildJobB

ChildJobA, ChildJobA-A

ChildJobA-A, ChildJobA-A-A

JOBS:

ParentJobA = PartA

ChildJobA = PartB

ChildJobB = PartC

ChildJobA-A = PartD

ChildJobA-A-A = PartE

MATLREQ:

ParentJobA = MaterialA

ParentJobA = MaterialB

ParentJobA = MaterialC

ChildJobA = MaterialD

ChildJobB = MaterialE

ChildJobA-A = MaterialF

ChildJobA-A-A = MaterialG

ChildJobA-A-A = MaterialH

After grabbing the Appropriate Parts from (JOB) table and Materials from (MATLREQ) table would become this (Shown Below), A Complete Parent/Child Table of Parts based on the Example data above

PartA, PartB

PartA, PartC

PartA, MaterialA

PartA, MaterialB

PartA, MaterialC

PartB, PartD

PartB, MaterialD

PartD, PartE

PartD, MaterialF

PartE, MaterialG

PartE, MaterialH

PartC, MaterialE

Perhaps im not explaining it well, But I'm not able to get them to relate completely, the best ive done is an Insane amount of LOOKUP() but it take 50 Minutes to run the load script, and when there are duplicates, Lookup does not catch them since it takes first matched value I believe, And im not able to use an applymapping either as there are repeat values (Either a Job has multiple Parts/Materials, or the same Part/Material is used in multiple jobs)

Any help or direction would be greatly appreciated

[kkkumar82]

Did you tried with Joins or applymap

[Colin-Albert]

Have you seen this blog on BOMs? Bill of Materials

[#4]

Joins are a problem.. I would have to load the Job Field from both tables (JOBS and MATLREQ) as Parent_Job and Component_Job.. However, i believe this will create a Loop during the load script and cause for loosely coupled tables

ApplyMap() is not a viable option as it only returns a single value

BILLJOBS table has all of my Parent-Child relationships for Jobs..

JOBS table holds all of the part information for a given Job (Parent and Child can be found here)

MATLREQ hols all the materials used to make a part in a single job (This can be a one-many relationship)

I must connect these based on Job Number, and the end result must be a Parent-Child table of Parts (including materials as parts)

[#5]

I have, And i dont believe that method Applies, as his BoM is already in a single table.. Whereas mine is broken down into

BILLJOBS table has all of my Parent-Child relationships for Jobs..

JOBS table holds all of the part information for a given Job (Parent and Child can be found here)

MATLREQ hols all the materials used to make a part in a single job (This can be a one-many relationship)

I must connect these based on Job Number, and the end result must be a Parent-Child table of Parts (including materials as parts)

[jonathandienst]

Perhaps I do not understand your problem. Why can you not simply join the tables on the two keys? Like this:

BILLJOB:

SQL SELECT "Component_Job",

    "Last_Updated",

    "Manual_Link",

    "Parent_Job",

    "Relationship_Qty",

    "Relationship_Type",

    "Root_Job";

    SQL SELECT "Component_Job",

    "Last_Updated",

    "Manual_Link",

    "Parent_Job",

    "Relationship_Qty",

    "Relationship_Type",

    "Root_Job"

FROM PRODUCTION.dbo."Bill_Of_Jobs";

Left Join (BILLJOB)

LOAD

    "Top_Lvl_Job" as Root_Job,

    Job,

    "Part_Number",

    Rev,

    Status;

SQL SELECT Job,

    "Part_Number",

    Rev,

    Status,

    "Top_Lvl_Job"

FROM PRODUCTION.dbo.Job WHERE Status Like '%Temp%';

Left Join (BILLJOB)

LOAD Job,

    Material,

    "Material_Req",

    "Material_ReqKey",

    "Quantity_Per";

SQL SELECT Job,

    Material,

    "Material_Req",

    "Material_ReqKey",

    "Quantity_Per"

FROM PRODUCTION.dbo."Material_Req";

[jonathandienst]

Or perhaps you are having problem with the hierarchy of components? You may need to load the first table with  a hierarchy load. See this article for more information on how to do that: Unbalanced, n-level hierarchies

[#8]

Unfortunately, the ERP System we are moving to does not associate Bill of Material based on Job Numbers.. Its associated by Part Numbers.. On top of this, Our current ERP system has our Parts and Materials broken up into seperate tables, hence the "Jobs" and "Materials" (You would think that values would be unique to their respective tables, but it is possible for a Part to be in both the "Jobs" table as well as the "Materials" table

Because of this Unique structure the current BILLJOBs table i have shown does not actually reflect all of the child parts, Simply the Final produce and all part we are producing that go into it.. It does not take into consideration any raw materials used in the process. I can relate the JOBS table to the BILLJOB table and get the part numbers associated for that, I however am not able to link the raw materials in and still keep it to a 2 Column Parent/Child table

I dont believe a Hierarchy load will work either, as both the Parts and Materials can be used in different places, I believe that article (Or the comments) mentions issues with 1-Many relationships

[swuehl]

Maybe something along these lines?

JOBS:
MAPPING
LOAD * INLINE [
F1, F2
ParentJobA, PartA
ChildJobA, PartB
ChildJobB, PartC
ChildJobA-A, PartD
ChildJobA-A-A, PartE
];


BILLJOBS:
LOAD ApplyMap('JOBS',Parent) as Parent, ApplyMap('JOBS',Child) as Child
INLINE [
Parent, Child
ParentJobA , ChildJobA
ParentJobA, ChildJobB
ChildJobA, ChildJobA-A
ChildJobA-A, ChildJobA-A-A
];


MATLREQ:
LOAD *
  Where Exists(Parent) or Exists(Child, Parent);
LOAD Applymap('JOBS',Job) as Parent, Material as Child INLINE [
Job, Material
ParentJobA , MaterialA
ParentJobA , MaterialB
ParentJobA , MaterialC
ChildJobA , MaterialD
ChildJobB , MaterialE
ChildJobA-A , MaterialF
ChildJobA-A-A , MaterialG
ChildJobA-A-A , MaterialH
];

[#10]

Thank you for this, While this directly did not solve my problem, It provided the insight i needed to get it working.

One of those moments where you can't believe you didn't figure it out sooner...

Thank you for that code visual swuehl, It was a big help!!