Need to create comparison difference table on load

[#1]

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!!

[pover · Accepted solution]

Here's a solution that involves doing a left join that causes a cross product that can be used to compare apples.

[#3]

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

[pover]

Nancy, Here's an improved version based.

Karl

[#5]

Please check attachment. This one looks like my original. Thx.

[pover]

Check this attachment.  In the comparison table orchard A should have 6 values instead of 12 like the original.

Karl

[#7]

Yes, great, thanks!