Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need to create comparison difference table on load

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:

OrchardTypeWgtRating
AFuji.323
AGala.274
AGranny.314
ARed Del.283
BFuji.332
BGala.195
BGranny.324
BRed Del.355
CFuji.334
CGranny.293
CRed Del.205

Resulting table should look like this :

OrchardApple AApple BWeight DifRating Dif
AFujiGala0.05-1
AFujiGranny0.01-1
AFujiRed Del0.040
AGalaGranny-0.040
AGalaRed Del-0.011
AGrannyRed Del0.031
BFujiGala0.14-3
BFujiGranny0.01-2
BFujiRed Del-0.02-3
BGalaGranny-0.131
BGalaRed Del-0.160
BGrannyRed Del-0.03-1
CFujiGranny0.041
CFujiRed Del0.13-1
CGrannyRed Del0.09

-2

Thanks in advance!!

1 Solution

Accepted Solutions
pover
Luminary Alumni
Luminary Alumni

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

View solution in original post

6 Replies
pover
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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
Luminary Alumni
Luminary Alumni

Nancy, Here's an improved version based.

Karl

Not applicable
Author

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

pover
Luminary Alumni
Luminary Alumni

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

Karl

Not applicable
Author

Yes, great, thanks!