Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

imsushantjain
Contributor

How to do Matrix Multiplication in QlikSense ?

Matrix A

A1A2A3A4A5
124010

Matrix B

B1B2B3B4B5
112123
234012
356201
412021
513012

c11 = 2 x 1 + 4 x 3 + 0 x 5 + 1 x 1 + 0 x 1 = 15

C1C2C3C4C5
1150000

c12 = 2 x 2 + 4 x 4 + 0 x 6 + 1 x 2 + 0 x 3 = 22

C1C2C3C4C5
11522000

c13 = 2 x 1 + 4 x 0 + 0 x 2 + 1 x 0 + 0 x 0 = 2

C1C2C3C4C5
11522200

c14 = 2 x 2 + 4 x 1 + 0 x 0 + 1 x 2 + 0 x 1 = 10

C1C2C3C4C5
115222100

c15 = 2 x 3 + 4 x 2 + 0 x 1 + 1 x 1 + 0 x 2 = 15

C1C2C3C4C5
1152221015

Result:

C1C2C3C4C5
1152221015

How to achieve such multiplication? Can i do it backend ? In excel it can be implemented using MMULT fucntion

1 Solution

Accepted Solutions
MVP
MVP

Re: How to do Matrix Multiplication in QlikSense ?

Actually it can be made flexible and generic by keeping all of the matrices in an unpivoted form or sparse matrix form. Then it works very well with larger matrices too and very quick I must say. The measure is simply Sum( A * B ) and you can visualize it in a Pivot Table with two dimensions and the measure:

2018-08-01 13_52_13-Qlik Sense Desktop.png

The source has to provide every matrix in the table structure like this:

x, y, value

Only the first column x should be a connecting field (column) in Qlik - a key field.

So the load script is much simpler too:


c=1000;

r=500;


// Generate a random matrix A with size c x r

MAT_A:

LOAD

  Floor((RecNo()-1)/$(c))+1 AS Ay,

  Mod( (RecNo()-1) , $(c))+1 AS x,

  If( Floor((RecNo()-1)/$(c))+1 < $(r)+1 , Floor(Rand()*9)) AS A

AUTOGENERATE

  $(c)*$(r);

;


// Generate a random matrix B with size c x c

MAT_B:

LOAD

  Floor((RecNo()-1)/$(c))+1 AS x,

  Mod( (RecNo()-1) , $(c))+1 AS By,

  Floor(Rand()*9) AS B

AUTOGENERATE

$(c)*$(c);

;

8 Replies
imsushantjain
Contributor

Re: How to do Matrix Multiplication in QlikSense ?

Currently i have tried to convert matrix into a table and tried achieving the same result. But the calculation is complicated!

MVP
MVP

Re: How to do Matrix Multiplication in QlikSense ?

Maybe you can use something like

Matrix Multiplication Using SQL | The Right Join

Here you create your matrices in tables with x / y coordinates explicitely expressed in your record values, a record per point of your matrix.

For the inner JOIN of the two tables, remember that Qlik uses same field names to create JOIN keys, so you would need to rename / swap column names of the second table - or transpose the matrix when loading in.

MVP
MVP

Re: How to do Matrix Multiplication in QlikSense ?

If you need matrix operations you might be better off using the AAI option in Qlik to allow you to call functions in Python or R and get a full set of functions by using the right Python or R libraries.

AAI = Advanced Analytics Integration, which take advantage of the Server Side Extension SSE protocol that Qlik supports.

Server Side Extensions (SSE)

https://github.com/qlik-oss/server-side-extension

https://github.com/qlik-oss/sse-r-plugin

MVP
MVP

Re: How to do Matrix Multiplication in QlikSense ?

It is possible to do matrix multiplication as you can see from the application I have attached and show screenshots from taking advantage of the associative datamodel in Qlik. Depending on requirements this might be way to inflexible and static but it is definitely a valid way of doing matrix multiplication.

Some transformation is needed to make the first matrix easy to calculate with by rotating the table.

2018-07-31 10_39_22-Qlik Sense Desktop.png

2018-07-31 10_42_37-Film og TV.png

This is more a type of demo of the possibiltiy of doing it than something you might want to use in production...

imsushantjain
Contributor

Re: How to do Matrix Multiplication in QlikSense ?

Hi Peter,

Thanks a lot for putting so much effort on my problem and the solution looks promising.

But my matrix is going to be minimum 5000*5000 and will increase, so the solution should be scalable and dynamic, hence doing more research to pin down the best possible solution. It would have been so much easier using Advanced Analytics Integration with Python numpy library but currently i don't have that luxury.

I also found another thread that deals with the similar issue Matrix Algebra

Regards

Sushant

MVP
MVP

Re: How to do Matrix Multiplication in QlikSense ?

Actually it can be made flexible and generic by keeping all of the matrices in an unpivoted form or sparse matrix form. Then it works very well with larger matrices too and very quick I must say. The measure is simply Sum( A * B ) and you can visualize it in a Pivot Table with two dimensions and the measure:

2018-08-01 13_52_13-Qlik Sense Desktop.png

The source has to provide every matrix in the table structure like this:

x, y, value

Only the first column x should be a connecting field (column) in Qlik - a key field.

So the load script is much simpler too:


c=1000;

r=500;


// Generate a random matrix A with size c x r

MAT_A:

LOAD

  Floor((RecNo()-1)/$(c))+1 AS Ay,

  Mod( (RecNo()-1) , $(c))+1 AS x,

  If( Floor((RecNo()-1)/$(c))+1 < $(r)+1 , Floor(Rand()*9)) AS A

AUTOGENERATE

  $(c)*$(r);

;


// Generate a random matrix B with size c x c

MAT_B:

LOAD

  Floor((RecNo()-1)/$(c))+1 AS x,

  Mod( (RecNo()-1) , $(c))+1 AS By,

  Floor(Rand()*9) AS B

AUTOGENERATE

$(c)*$(c);

;

imsushantjain
Contributor

Re: How to do Matrix Multiplication in QlikSense ?

Hi Peter,

I am sorry to reply so late due to other priority i wasn't able to implement your idea, but the solution you build is logically correct but it is definitely consuming a lot of system memory.

Pivot Table Error.PNG

I will do more analysis and come back on this.

Regards

Sushant

MVP
MVP

Re: How to do Matrix Multiplication in QlikSense ?

I agree - Qlik is not the right tool for this - although it is possible it is not practical and advisable. Use integration technologies like Advance Analytics Integration to do what you want to do instead....

Community Browser