Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have this chart:
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?
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...
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.
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
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
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!
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:
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 ...)
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...
I agree. That's why I mentioned R above.
I will try with R. Thank you Jonathan and Gysbert!