topic Need to create comparison difference table on load in QlikView

Need to create comparison difference table on load

Fri, 20 Jan 2012 17:58:54 GMT

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

Re: Need to create comparison difference table on load

pover — Fri, 20 Jan 2012 19:18:49 GMT

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

Need to create comparison difference table on load

Fri, 20 Jan 2012 22:17:29 GMT

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

Re: Need to create comparison difference table on load

pover — Fri, 20 Jan 2012 22:40:53 GMT

Nancy, Here's an improved version based.

Karl

Need to create comparison difference table on load

Fri, 20 Jan 2012 23:02:31 GMT

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

Re: Need to create comparison difference table on load

pover — Fri, 20 Jan 2012 23:08:15 GMT

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

Karl

Need to create comparison difference table on load

Mon, 23 Jan 2012 15:41:37 GMT

Yes, great, thanks!