Discussion Board for collaboration related to Creating Analytics for QlikView.
Does anyone know if qlikview has the ability to multiply out matrices (something equivalent to =MMULT in excel)?
We are trying to multiply a fixed (50x50) matrix with a slider-controlled 50x1 price vector.
A B C
A 1 3 2
B 2 1 2
C 5 1 0
Price vector (i.e. Slider sets A to 2)
A =1*2 + 3*0 + 2*0 = 2
B =2*2 + 1*0 + 2*0 = 4
C= 5*2 + 1*0 + 0*0 = 10
Have thought about writing the whole formula out but it does not seem possbile to specify particular cells (just columns).
Would appreciate any advice/insight.
you could get specific 'fields' with a sorted table and peek to defined rows.
What is the formula besides the syntax of Excel or QlikView?
I am afraid not.
I think the best way is to transform your scalar operation into a simple linear operation.
In QV, you have a Crosstable interpretation, transform your matrix into the following:
AA, AB, AC, BA, BB, BC, CA, CB, CC1, 3, 2, 2, 1, 2, 5, 1, 0
2, 0, 0
AA*A, AB*B, AC*C BA*A, BB*B, BC*C, CA*A, CB*B, CC*C (edited: corrected the appropriate vector index)
You will have to code...
Hope this helps
(and let me know if you found a faster way )
Does the below help you?
Data1:LOAD * INLINE [ A, B, C 1, 3, 2 2, 1, 2 5, 1, 0];
Data2:Load * INLINE [ pA, pB, pC 2, 0, 0];
let nRows1 = NoOfRows('Data1')-1;let nRows2 = NoOfRows('Data2')-1;
for i = 0 to nRows1 let vA = peek('A',$(i),'Data1'); let vB = peek('B',$(i),'Data1'); let vC = peek('C',$(i),'Data1'); for j = 0 to nRows2 let vpA = peek('pA',$(j),'Data2'); let vpB = peek('pB',$(j),'Data2'); let vpC = peek('pB',$(j),'Data2');
Data3: load ($(vA) * $(vpA)) + ($(vB) * $(vpB)) + ($(vC) * $(vpC)) as xA AutoGenerate(1);
next j; next i;
I had a similar requirement as with original poster. The post by Sajeevan Govindan helped.
I am in similar situation, any update on Matrix Multiplication would be very helpful!
Any pointer for matrix as large as 5000x5000 and can increase dynamically, would you recommend this solution?
i believe i have to even loop statement likes let vA = peek('A',$(i),'Data1'); but that will increase the complexity as well.