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

Compare all columns on chart

I have this chart:

chart.JPG

and I need to compare all columns with all columns.

Ex:

Comparison 1: PROD 7 with PROD 5

Comparison 2: PROD 7 with PROD 4

Comparison 3: PROD 7 with PROD 3

Comparison 4: PROD 7 with PROD 1

Comparison 5: PROD 7 with PROD 6

Comparison 6: PROD 7 with PROD 2

Comparison 7: PROD 5 with PROD 4

Comparison 8: PROD 5 with PROD 3

Comparison 9: PROD 5 with PROD 1

Comparison 10: PROD 5 with PROD 6

Comparison 1: PROD 5 with PROD 2

....

but the number of columns is not fixed.

Someone have any idea how I can do this?

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

Are you familiar with R ?  You could call an R script on the fly to do a cluster analysis to create the dynamic groupings on the fly and send them back to QlikView. 

I sense that this would  need some kind of script, macro script to crunch the groupings and return them back. And i've heard that R is common for this kind of thing. Not an expert myself though...

View solution in original post

8 Replies
Gysbert_Wassenaar

Load your table twice, but rename the fields for the second table so that you create a cartesian product. Then use a pivot table to create a matrix where you can compare the values in.

This can perform horrible with largish data sets. But I think it's either that or use a proper statistical tool like R to create a decent correlation matrix.


talk is cheap, supply exceeds demand
datanibbler
Champion
Champion

Hi Cintia,

that is a good one.

I can only think of a first step right now:

- If you have 7 products, you need (7x6=) 42 comparisons, right?

=> So you need a loop nested inside a loop to generate all the combinations first.

You can first determine the nr. of products you have by aggregating your table.

Sth. like

>>> FOR i = 1 TO 7

         FOR i2 = 2 TO 7

          LET v_Varname = 'combination_' & $(i) & '|' & $(i2);

          LET v_VarValue = 'Product_' & $(i) & '_Product_' & $(i2);

          LET $(v_Varname) = v_VarValue

       NEXT

     NEXT

That should generate all 42 necessary combinations for you.

Try this first and I guess there'll be a way to go from there.

HTH

JonnyPoole
Employee
Employee

What do you envision for a visual display ? 

You can create a table in the model that generates all combination (cross product query sorted for unique combinations) and then you can group/cluster the combos how you see fit.

This could be displayed in a trellis chart , or maybe a 'grouped' bar chart or anything that allows 2 dimensions... but it would help if you had a visual idea of the intended display

buzzy996
Master II
Master II

i think u can create 2 variables with 2 list boxes and place all ur products on each variable.

and select any product from variable1 and select any product from varibale2 & see the same comparison on ur chart!

Not applicable
Author

I have to apply the tukey test in this chart, when statistically the product one is different of the product two (7.84 difference in this case) I have to place diferent letter. in theory it will be something like this:

chart2.jpg

PROD 7, PROD 5, PROD 4, PROD 3, PROD 1 and PROD 6 are equals because the difference between them is smaller than 7.84 analyzing from left to right (PROD 7 with the others)

but PROD 1, PROD 6 and PROD 2 are equal too... analyzing from right to left (PROD 2 with the others)

Something like this:

(left to right)

PROD 7 - PROD 5 = A

PROD 7 - PROD 4 = A

PROD 7 - PROD 3 = A

PROD 7 - PROD 1 = A

PROD 7 - PROD 6 = A

PROD 7 - PROD 2 = B


(right to left)

PROD 2 NOW IS B

PROD 2 - PROD 6 = B

PROD 2 - PROD 1 = B


and this sequence will apply every time there is a change of the letters (A, B, C, D ...)

JonnyPoole
Employee
Employee

Are you familiar with R ?  You could call an R script on the fly to do a cluster analysis to create the dynamic groupings on the fly and send them back to QlikView. 

I sense that this would  need some kind of script, macro script to crunch the groupings and return them back. And i've heard that R is common for this kind of thing. Not an expert myself though...

Gysbert_Wassenaar

I agree. That's why I mentioned R above.


talk is cheap, supply exceeds demand
Not applicable
Author

I will try with R. Thank you Jonathan and Gysbert!