Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Bill of Materials (Joining)

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
9 Replies
kkkumar82
Specialist III
Specialist III

Did you tried with Joins or applymap

Colin-Albert

Have you seen this blog on BOMs? Bill of Materials

Not applicable
Author

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)

Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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";

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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
MVP
MVP

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

];

Not applicable
Author

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!!