1 Reply Latest reply: Jul 3, 2012 1:21 AM by Claudia Zwick RSS

    ReDimension and prepare for correlation



      I have a table like this:


      Code Part CorrelationNumber

      AA    100  150

      CC    100  280

      BB    200  170

      AA    200  300



      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?