In total, table has cca 500.000 lines and 995 unique Codes with 320 unique Parts. The trick is that every code do not have every part in it. One code builds one product with cca 40 parts. THe thing is that I would like to calculate correlation between parts. I have the column: CorrelationNumber but the problem is that some parts exists in 500 codes, and other in 400 for example. What I would like to do is to make some kind of agregation which will include all codes and attach one part to every code but CorrelationNumber will be 0 in case that one part does not exists in that code. In that case i could have:
Code Part CorrelationNumber Code Part CorrelationNumber
AA 100 150 AA 200 300
BB 100 0 BB 200 170
CC 100 280 CC 200 0
In this example I have two ranges with all codes, two different parts (100 and 200) but code BB does not include part 200 acctualy (that's why it has 0 correlation number)
How to get two ranges with all codes where parts that actually do not exist in code has CorrelationNumber=0 adn parts that exists have their CorrelationNumbers. Is it possible to change something during load or it is possible with some formula?