- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to create a combination matrix
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!
- Tags:
- combinations
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Read more at Data Voyagers - datavoyagers.net
Follow me on my LinkedIn | Know IPC Global at ipc-global.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Read more at Data Voyagers - datavoyagers.net
Follow me on my LinkedIn | Know IPC Global at ipc-global.com