Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Re: Need to create comparison difference table on load

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

6 Replies
Luminary
Luminary

Re: Need to create comparison difference table on load

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

Not applicable

Need to create comparison difference table on load

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

Luminary
Luminary

Re: Need to create comparison difference table on load

Nancy, Here's an improved version based.

Karl

Not applicable

Need to create comparison difference table on load

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

Luminary
Luminary

Re: Need to create comparison difference table on load

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

Karl

Not applicable

Need to create comparison difference table on load

Yes, great, thanks!