Dear all,
I am trying to generate a material report for different product lines. Unfortunately, when creating the references in our system (different suppliers giving the same product specification on a different part number), the links between specific products was not made in a simple way and now I am struggling building the report as the data needs to be cleaned.
Here is my issue:
We have different product lines (example data): screws, nuts and bolts. Each of these product lines have different models with the same product specification all referenced with a specific part number (due to the different supplier).
My aim is to create a primary key for all parts that are so called interchangeable (same form, fit and function).
here under, is the extract of data I get (sample, real data is more than 20,000 lines)
As you will see, some parts have the same description but are not interchangeable.
I was trying to generate a model following the principle if A = B and B = C then A = C but i did not manage to reach my goal yet.
Would you be able to help me?
Thank you for your support!
Ronan
data extracted
<colgroup><col span="5"></colgroup><tbody>
</tbody>
Data expected after cleaning
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>
I am trying to generate a material report for different product lines. Unfortunately, when creating the references in our system (different suppliers giving the same product specification on a different part number), the links between specific products was not made in a simple way and now I am struggling building the report as the data needs to be cleaned.
Here is my issue:
We have different product lines (example data): screws, nuts and bolts. Each of these product lines have different models with the same product specification all referenced with a specific part number (due to the different supplier).
My aim is to create a primary key for all parts that are so called interchangeable (same form, fit and function).
here under, is the extract of data I get (sample, real data is more than 20,000 lines)
As you will see, some parts have the same description but are not interchangeable.
I was trying to generate a model following the principle if A = B and B = C then A = C but i did not manage to reach my goal yet.
Would you be able to help me?
Thank you for your support!
Ronan
data extracted
PN1 | description1 | PN2 | description2 | |
UE37 | screw | DY42 | screw | |
MT75 | screw | |||
YN66 | screw | |||
PO87 | screw | |||
UV75 | screw | |||
DY42 | screw | UE37 | screw | |
BP13 | screw | |||
PW58 | screw | |||
YN66 | screw | YU44 | screw | |
PO87 | screw | |||
UV75 | screw | |||
UT14 | screw | XL43 | screw | |
YD50 | screw | |||
YN66 | screw | |||
KM91 | nut | RM13 | nut | |
GJ29 | nut | |||
BK96 | nut | |||
CZ89 | nut | |||
EO41 | nut | |||
CQ81 | nut | VH75 | nut | |
TT67 | nut | |||
KM91 | nut | |||
KI49 | bolt | PN10 | bolt | |
HW91 | bolt | |||
JL63 | bolt | |||
TH20 | bolt | PR55 | bolt | |
PN10 | bolt | |||
ZT51 | bolt | |||
YE57 | bolt | |||
BY10 | bolt | |||
JL63 | bolt |
<colgroup><col span="5"></colgroup><tbody>
</tbody>
Data expected after cleaning
PN1 | description1 | PN2 | description2 | Unique product line ID |
UE37 | screw | DY42 | screw | S001 |
MT75 | screw | S001 | ||
YN66 | screw | S001 | ||
PO87 | screw | S001 | ||
UV75 | screw | S001 | ||
DY42 | screw | UE37 | screw | S001 |
BP13 | screw | S001 | ||
PW58 | screw | S001 | ||
YN66 | screw | YU44 | screw | S001 |
PO87 | screw | S001 | ||
UV75 | screw | S001 | ||
AX32 | screw | ZA55 | screw | S002 |
XD40 | screw | S002 | ||
AA23 | screw | S002 | ||
KM91 | nut | RM13 | nut | N001 |
GJ29 | nut | N001 | ||
BK96 | nut | N001 | ||
CZ89 | nut | N001 | ||
CQ81 | nut | VH75 | nut | N001 |
TT67 | nut | N001 | ||
KM91 | nut | N001 | ||
EO41 | nut | OE23 | nut | N002 |
KI49 | bolt | PN10 | bolt | B001 |
HW91 | bolt | B001 | ||
JL63 | bolt | B001 | ||
TH20 | bolt | PR55 | bolt | B001 |
PN10 | bolt | B001 | ||
ZT51 | bolt | B001 | ||
YE57 | bolt | B001 | ||
BY10 | bolt | B001 | ||
JL63 | bolt | B001 |
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>