topic Re: Compare all columns on chart in QlikView

Compare all columns on chart

Wed, 18 Mar 2015 14:57:00 GMT

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?

Re: Compare all columns on chart

Gysbert_Wassenaar — Wed, 18 Mar 2015 15:05:47 GMT

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.

Re: Compare all columns on chart

datanibbler — Wed, 18 Mar 2015 15:06:36 GMT

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

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

Re: Compare all columns on chart

JonnyPoole — Wed, 18 Mar 2015 15:07:04 GMT

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

Re: Compare all columns on chart

buzzy996 — Wed, 18 Mar 2015 15:11:24 GMT

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!

Re: Compare all columns on chart

Wed, 18 Mar 2015 17:11:12 GMT

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 ...)

Re: Compare all columns on chart

JonnyPoole — Wed, 18 Mar 2015 17:18:37 GMT

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

Re: Compare all columns on chart

Gysbert_Wassenaar — Wed, 18 Mar 2015 17:26:46 GMT

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

Re: Compare all columns on chart

Thu, 19 Mar 2015 12:02:30 GMT

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