4 Replies Latest reply: May 12, 2014 1:20 PM by Roy Alcalde RSS

    Matrix Algebra

      Hi all,

       

      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.

       

      i.e.

       

      Matrix                                 

       

            A     B      C

        1      3      2

       

      B    2      1      2 

       

      C    5      1      0

       

      Price vector (i.e. Slider sets A to 2)

       

      A  2

       

      B  0

       

      C 0

       

      Ouput:

       

      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.

       

      Thanks!

        • Re: Matrix Algebra

          Hi James,

           

          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?

           

          Ciao

          Klaus

          • Re: Matrix Algebra
            Antoine Frangieh

            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, CC
            1, 3, 2, 2, 1, 2, 5, 1, 0

             

            A,B,C

            2, 0, 0

             

            Resultante:

            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 )

             

            Cheers,


            Antoine

            • Re: Matrix Algebra
              Sajeevan Govindan

              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;