topic Re: Matrix multiplication in QlikView

Matrix multiplication

ronnywaage — Sun, 25 May 2014 09:19:34 GMT

I was searching the community for standard matrix functions but couldn't find any out of the box functions. I have seen some solutions where you are looping through the rows and columns. I have not tested performance for these but my hypothesis is that it gives a better performance with join statements than looping through the elements. And I have noticed that there are a couple of questions out there for matrix functions.

Just for fun I have tested out a generic matrix multiplication function based on an idea I got this weekend. I have tested it with a few simple test cases but will continue to test it, both for the calculations and performance. I am just sharing it here if someone else is currently looking into the same issues.

Sorry if this approach is overlapping with others earlier postings.

The first step is to create two matrices on the form [row, column, value]. Below you can see an example with two 2x2 matrices:

A:
LOAD * Inline
[a_row, a_col, a_value
1, 1, 1
1, 2, 2
2, 1, 3
2, 2, 4];

B:
LOAD * Inline
[b_row, b_col, b_value
1, 1, 5
1, 2, 6
2, 1, 7
2, 2, 8];

Then it is just to call the function for multiply matrix A and B. The result will be a new matrix C.

CALL Mat_Mult;

I have not included any check for correct matrix dimensions or other checks for simplification of the code.

SUB Mat_Mult()

temp_C:
LOAD
a_row as c_a_row,
a_col as c_col,
a_value as c_value_1
Resident A;

join (temp_C)
LOAD
b_row as c_col,
b_col as c_b_col,
b_value as c_value_2
Resident B;

C:
LOAD
c_a_row as c_row,
c_b_col as c_col,
sum(c_value_1 * c_value_2) as c_value
Resident temp_C
group by c_a_row, c_b_col;

DROP table temp_C;

ENDSUB

This is a generic function so the two matrices can have any size as long as the numbers of columns in A is matching the number of rows in B. It even seems to work with sparse matrix (where only a few elements have a value and the rest of the elements are 0).

To transpose a matrix on this format is also easy. Below is a function to transpose the output from the previous function (the C matrix).

SUB Mat_Transpose()

😧
LOAD
c_col as d_row,
c_row as d_col,
c_value as d_value
Resident C;

ENDSUB

Please let me know if you have any comments or questions.

Re: Matrix multiplication

marcus_sommer — Sun, 25 May 2014 16:46:45 GMT

I think you should also try mapping - it could be faster then joining and easier in scripting (in real case are only two loads as here with inline loads).

A0:
LOAD * Inline
[a_row, a_col, a_value
1, 1, 1
1, 2, 2
2, 1, 3
2, 2, 4];

A1:

Mapping LOAD a_row &'|'& a_col as KEY, a_value Resident A0;

B0:
LOAD * Inline
[b_row, b_col, b_value
1, 1, 5
1, 2, 6
2, 1, 7
2, 2, 8];

B1:

LOAD b_row as row, b_col as col, applymap('A1', a_row &'|'& a_col, 0) * b_value as value Resident B0;

- Marcus

Re: Matrix multiplication

ronnywaage — Sun, 25 May 2014 21:28:23 GMT

Hi Marcus,

Thanks for your input. My first priority this weekend was to check if my theory/idea worked. And it did, at least for my test cases. The next step will be more complex tests and performance since in the end I will see if it still works with matrix containing millions of elements. I will also see if apply mapping can be used in any ways, as you suggested, to improve performance or simplify the code.

So to your solution. I assume you meant b_row and b_col in the applymap in the B1 load. I corrected and tested it and it worked well for an element-vice multiplication.

But what I am trying to acheive is a matrix multiplication. For a 2x2 * 2x2 multiplication the output element (c11) should be a11*b11 + a12*b21, not just a11*b11.

- Ronny

Re: Matrix multiplication

marcus_sommer — Mon, 26 May 2014 10:06:14 GMT

Hi Ronny,

yes you are right the key within applymap wasn't correct, just copy+paste from code above ...

Another approach to build a matrix could be to concat the values (manually value1 & '|' & value2 ... or per concat-function) and read these values later with subfield() - so you could create some kind of an array.

- Marcus