Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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
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.
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