Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Greetings Community!
I need to load a table from a resident table that compares each possible [Type] within a group and calculates the difference for each metric.
I have attached a sample file using types of apples. I have a table with multiple apple types within an orchard. I want to show the difference between each metric for each apple combination within each orchard. Can anyone help me with script to accomplish this.
Data table looks like this:
Orchard | Type | Wgt | Rating |
A | Fuji | .32 | 3 |
A | Gala | .27 | 4 |
A | Granny | .31 | 4 |
A | Red Del | .28 | 3 |
B | Fuji | .33 | 2 |
B | Gala | .19 | 5 |
B | Granny | .32 | 4 |
B | Red Del | .35 | 5 |
C | Fuji | .33 | 4 |
C | Granny | .29 | 3 |
C | Red Del | .20 | 5 |
Resulting table should look like this :
Orchard | Apple A | Apple B | Weight Dif | Rating Dif |
A | Fuji | Gala | 0.05 | -1 |
A | Fuji | Granny | 0.01 | -1 |
A | Fuji | Red Del | 0.04 | 0 |
A | Gala | Granny | -0.04 | 0 |
A | Gala | Red Del | -0.01 | 1 |
A | Granny | Red Del | 0.03 | 1 |
B | Fuji | Gala | 0.14 | -3 |
B | Fuji | Granny | 0.01 | -2 |
B | Fuji | Red Del | -0.02 | -3 |
B | Gala | Granny | -0.13 | 1 |
B | Gala | Red Del | -0.16 | 0 |
B | Granny | Red Del | -0.03 | -1 |
C | Fuji | Granny | 0.04 | 1 |
C | Fuji | Red Del | 0.13 | -1 |
C | Granny | Red Del | 0.09 | -2 |
Thanks in advance!!
Here's a solution that involves doing a left join that causes a cross product that can be used to compare apples.
Here's a solution that involves doing a left join that causes a cross product that can be used to compare apples.
Thanks Karl. Simple and effective. Is there an easy way, either in the script or the table properties, to eliminate the duplicate crosses (ie. Fuji vs Gala and Gala vs Fuji)?
Thanks, Nancy
Nancy, Here's an improved version based.
Karl
Please check attachment. This one looks like my original. Thx.
Check this attachment. In the comparison table orchard A should have 6 values instead of 12 like the original.
Karl
Yes, great, thanks!