How to create a combination matrix

[DEMONIO_AZUL]

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!

[amonjaras_c40 · Accepted solution]

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.

[marksouzacosta · Accepted solution]

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