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

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>

 

[Ronan_1]

Hi Bhos123,

PN1 and PN2 are not primary keys. The aim is to generate one primary key for the products that have a link that we can identify.
For example, we have A = B, B = C then A = C. Say for product A we have allocated a random unique primary key, for this example 1. Say that A, B and C are then allocated the primary key 1.

Ronan

 

[Ronan_1]

Hello everyone,

Anyone having an idea on how to solve this topic?

thank you for your help!

Ronan

 

[Ronan_1]

Hi everyone,

Anyone creative to help me solving this question?
I have exhausted all my skills there...

Thank you for you help!

ronan

 

[Ronan_1]

Hi All,

it would be great if someone had an idea on this topic. I am really lost here...

Thank you for your help!

Ronan