Material reporting - interchangeability - product line versus product depth

[Ronan_1]

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
[TABLE="width: 320"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD]PN1[/TD]
[TD]description1[/TD]
[TD]PN2[/TD]
[TD="colspan: 2"]description2[/TD]
[/TR]
[TR]
[TD]UE37[/TD]
[TD]screw[/TD]
[TD]DY42[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]MT75[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]YN66[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PO87[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]UV75[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DY42[/TD]
[TD]screw[/TD]
[TD]UE37[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]BP13[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PW58[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YN66[/TD]
[TD]screw[/TD]
[TD]YU44[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PO87[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]UV75[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]UT14[/TD]
[TD]screw[/TD]
[TD]XL43[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]YD50[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]YN66[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KM91[/TD]
[TD]nut[/TD]
[TD]RM13[/TD]
[TD]nut[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]GJ29[/TD]
[TD]nut[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]BK96[/TD]
[TD]nut[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]CZ89[/TD]
[TD]nut[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]EO41[/TD]
[TD]nut[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CQ81[/TD]
[TD]nut[/TD]
[TD]VH75[/TD]
[TD]nut[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]TT67[/TD]
[TD]nut[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]KM91[/TD]
[TD]nut[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KI49[/TD]
[TD]bolt[/TD]
[TD]PN10[/TD]
[TD]bolt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]HW91[/TD]
[TD]bolt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]JL63[/TD]
[TD]bolt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TH20[/TD]
[TD]bolt[/TD]
[TD]PR55[/TD]
[TD]bolt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PN10[/TD]
[TD]bolt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]ZT51[/TD]
[TD]bolt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]YE57[/TD]
[TD]bolt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]BY10[/TD]
[TD]bolt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]JL63[/TD]
[TD]bolt[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]





Data expected after cleaning
[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]PN1[/TD]
[TD="width: 64"]description1[/TD]
[TD="width: 64"]PN2[/TD]
[TD="width: 64"]description2[/TD]
[TD="width: 64"]Unique product line ID[/TD]
[/TR]
[TR]
[TD]UE37[/TD]
[TD]screw[/TD]
[TD]DY42[/TD]
[TD]screw[/TD]
[TD]S001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]MT75[/TD]
[TD]screw[/TD]
[TD]S001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]YN66[/TD]
[TD]screw[/TD]
[TD]S001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PO87[/TD]
[TD]screw[/TD]
[TD]S001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]UV75[/TD]
[TD]screw[/TD]
[TD]S001[/TD]
[/TR]
[TR]
[TD]DY42[/TD]
[TD]screw[/TD]
[TD]UE37[/TD]
[TD]screw[/TD]
[TD]S001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]BP13[/TD]
[TD]screw[/TD]
[TD]S001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PW58[/TD]
[TD]screw[/TD]
[TD]S001[/TD]
[/TR]
[TR]
[TD]YN66[/TD]
[TD]screw[/TD]
[TD]YU44[/TD]
[TD]screw[/TD]
[TD]S001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PO87[/TD]
[TD]screw[/TD]
[TD]S001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]UV75[/TD]
[TD]screw[/TD]
[TD]S001[/TD]
[/TR]
[TR]
[TD]AX32[/TD]
[TD]screw[/TD]
[TD]ZA55[/TD]
[TD]screw[/TD]
[TD]S002[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]XD40[/TD]
[TD]screw[/TD]
[TD]S002[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]AA23[/TD]
[TD]screw[/TD]
[TD]S002[/TD]
[/TR]
[TR]
[TD]KM91[/TD]
[TD]nut[/TD]
[TD]RM13[/TD]
[TD]nut[/TD]
[TD]N001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]GJ29[/TD]
[TD]nut[/TD]
[TD]N001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]BK96[/TD]
[TD]nut[/TD]
[TD]N001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]CZ89[/TD]
[TD]nut[/TD]
[TD]N001[/TD]
[/TR]
[TR]
[TD]CQ81[/TD]
[TD]nut[/TD]
[TD]VH75[/TD]
[TD]nut[/TD]
[TD]N001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]TT67[/TD]
[TD]nut[/TD]
[TD]N001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]KM91[/TD]
[TD]nut[/TD]
[TD]N001[/TD]
[/TR]
[TR]
[TD]EO41[/TD]
[TD]nut[/TD]
[TD]OE23[/TD]
[TD]nut[/TD]
[TD]N002[/TD]
[/TR]
[TR]
[TD]KI49[/TD]
[TD]bolt[/TD]
[TD]PN10[/TD]
[TD]bolt[/TD]
[TD]B001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]HW91[/TD]
[TD]bolt[/TD]
[TD]B001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]JL63[/TD]
[TD]bolt[/TD]
[TD]B001[/TD]
[/TR]
[TR]
[TD]TH20[/TD]
[TD]bolt[/TD]
[TD]PR55[/TD]
[TD]bolt[/TD]
[TD]B001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PN10[/TD]
[TD]bolt[/TD]
[TD]B001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]ZT51[/TD]
[TD]bolt[/TD]
[TD]B001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]YE57[/TD]
[TD]bolt[/TD]
[TD]B001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]BY10[/TD]
[TD]bolt[/TD]
[TD]B001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]JL63[/TD]
[TD]bolt[/TD]
[TD]B001[/TD]
[/TR]
</tbody>[/TABLE]

 

[Ronan_1]

Hi Everyone?

Would you have an idea how to solve this request?
I would really appreciate as I am completely blocked...
Thank you for your help!

Ronan

 

[bhos123]

why are you choosing S002 for AX32? why not S001?

 

[Ronan_1]

Hi Bhos123,

thank you for your question.
S001 and S002 are two different product lines. They cannot be mixed at all as they have completely different specifications and attributes.
AX32 cannot be replaced by any of the PN shown in the S001 product line but it has alternates in the S002 product line, therefore it is belonging to S002.

Ronan

 

[bhos123]

Now you have classified products into S001, S002 , N001 , B001..etc. what exactly you want to do next?

 

[Ronan_1]

The thing is that I do not have this classification. I would like to generate it from the the input dataset (data extracted). S001, S002, ... shall be the new classification. Until now, I just have columns PN1, Descr. 1, PN2, Descr. 2.

Ronan

 

[Ronan_1]

I do not manage to get a way to automatically go through the PN1 - PN2 lists and generate this classification automatically.

The thing is that I do not have this classification. I would like to generate it from the the input dataset (data extracted). S001, S002, ... shall be the new classification. Until now, I just have columns PN1, Descr. 1, PN2, Descr. 2.

Ronan

 

[bhos123]

You can generate the classification using the algorithm that you want to use to classify. We first need to mention it very clearly. Then we can do the coding to generate them.

 

[Ronan_1]

Thank you for your answer. Is it possible to have a unique primary key that is automatically generated?

 

[bhos123]

Is your PN1 your primary key?? or PN2?. If you want to extract unique values from a column. First copy the column and paste it in some other column, then select it and select 'Remove duplicates' from 'Data>>Data Tolls'. you will get the unique values.