topic Re: How to create a combination matrix in App Development

How to create a combination matrix

DEMONIO_AZUL — Mon, 24 Jun 2024 21:33:47 GMT

Hello there,

I am trying to create a matrix with values combined on a single field.

For data, I have a table of Values that contains A, B, C, D.

The expected outcome is the combination without swapped values of all of the items that Values has into a new field, named Combinations:

A-B, A-C, A-D, B-C, B-D, C-D.

(These values are not valid: A-A, B-B, C-C, D-D, B-A, C-A,C-B, D-A, D-B, D-C).

The results may also look as:

Any hints or a solution will be much appreciated. Thanks!

Re: How to create a combination matrix

amonjaras_c40 — Tue, 25 Jun 2024 02:07:49 GMT

Hello, Blue!

Load your field twice in different tables like this:

Table1:
load * Inline [
Field1
A
B
C
D
];

Table2:
load * Inline [
Field2
A
B
C
D
];

Then build your pivot table with Field1 as column and Field2 as row.

The measure would be: if(Field2>Field1,Field1&'-'&Field2)

The result is in the attached image.

Hope this is what you are looking for.

Santo.

Re: How to create a combination matrix

marksouzacosta — Tue, 25 Jun 2024 02:26:04 GMT

Or you can use a variation of @amonjaras_c40 solution and move everything to the Load Script. This way your Measure will be simpler.

TempMatrix: load * Inline [ Field1 A B C D ]; JOIN (TempMatrix) Table2: load * Inline [ Field2 A B C D ]; Matrix: LOAD Field1, Field2, If(Field2 > Field1, Field1 & '-' & Field2) AS Combination RESIDENT TempMatrix ; DROP TABLE TempMatrix;

Your Pivot Table settings will be like this:

The Measure is just: MaxString(Combination)

Regards,

Mark Costa