# Convert n x m matrix into two column table with n * m rows where col 2 of table has the matrix values listed downward

Hi,

Probability Matrix.xlsx (33.2 KB)

I am trying to convert a matrix into a table in KNIME. Ideally, I would like to start with “data input” (highlighted in yellow in excel attached) then calculate the matrix and then convert the matrix into a table (see sample output in excel sheet attached) which I can then use in my calculations in KNIME. However, the matrix itself quite a customized calculation.

My questions are:
(1) How can I go about the matrix calculation in KNIME?
(2) The more important question - assuming I cannot do the matrix calculation in KNIME (given it is quite customised), if I can prepare the matrix in excel and import it to KNIME, how can I convert the matrix into my desired output table.

Once I have the output table, I plan to look up the relevant probability using column 1 as the look up value.

Thanks,
Shivam

if you want to lookup a single element of the table, wouldn’t it be much easier (and quicker to execute) if you just calculate the value directly using the inputs age-when-entering-scheme and age-now? The math doesn’t look that complex, it appears to be a bunch of multiplications of the elements of the yellow input vector.

Something like this:
input vector a of length n
i … age start
j … age now
value = (1 - ai) * (1 - a(i+1)) * (1 - a(i+2)) * … * (1 - a(j-1)) * aj

4 Likes

Hi @ShivamChauhan
As @Thyme mentioned, the math isn’t complex. It is just a question of feeding the inputs to process the desired results in the right arrangement, and would be quicker to process the desired value on demand.

Aiming to process the whole table you would need to loop over all the ‘age start’ and correspondent deltas.

I prepared a workflow that process your customize probabilities table. From this workflow, you would be able to extract the node arrangement approach to move forward with the usability of your data, and fly away free from spreadsheet dependence 20220107_probability-matrix.knwf (179.1 KB)

BR

5 Likes

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.