Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Friends,
Suppose I have one databank about games of some sport. I have in Column1 the names of the first player and in Column2 the names of second player.
Since each name can appear in both columns (some games as first and some as second player), I want do build a table which tells me, for each name, how many times it appears in Column1, how many in Column2 and the sum of both columns.
For example, suppose that I have this data:
Column1 | Column2 |
---|---|
John | Mary |
Fred | Paul |
John | Paul |
Fred | Mary |
Mary | Paul |
John | Fred |
Thus, I wanted to get this table:
Name | Column1 | Column2 | Sum |
---|---|---|---|
John | 3 | 0 | 3 |
Mary | 1 | 2 | 3 |
Paul | 0 | 3 | 3 |
Fred | 2 | 1 | 3 |
How can I do it? Many thanks in advance.
Try this?
Table1:
LOAD * INLINE [
Column1, Column2
John, Mary
Fred, Paul
John, Paul
Fred, Mary
Mary, Paul
John, Fred
];
NoConcatenate
Table2:
LOAD Column1,
Count(Column1) AS Col1Cnt
Resident Table1
Group By Column1
Order By Column1;
JOIN(Table2)
Table3:
LOAD Column2 AS Column1,
Count(Column2) AS Col2Cnt
Resident Table1
Group By Column2
Order By Column2 ASC;
DROP TABLE Table1;
Add straight table and use
Dim: Column1
Expr: =Sum(Col1Cnt)
= Sum(Col2Cnt)
= RangeSum(Col1Cnt, Col2Cnt)
Thank you but, since I have many player names, this LOAD * INLINE routine isn't a viable option.
I have used Load * Inline because i have worked on your data sample you posted. Just use what ever script you are using to pull col1 and col2 then use the script from NoConcatenate. Make sure you change the column names accordingly. If possible share your app you are working on to look into?
OK. I will try. Thanks.
Maybe something like this
Create a subfield in the script like this
a:
LOAD Column1,Column2, SubField(Column1&'_'&'Column1'&'-'&Column2&'_'&'Column2','-') as Key INLINE [
Column1,Column2
John, Mary
Fred, Paul
John, Paul
Fred, Mary
Mary ,Paul
John, Fred
];
in the chart
Dimnesion : left(Key,findoneof(Key,'_')-1)
Exp1: count({<Key={"*Column1*"}>}Key)
Exp2: count({<Key={"*Column2*"}>}Key)
Total : Column(1)+ Column(2)
Hi, I finally could try this and it worked fine! Thanks!
But there is still two few questions I hope anyone can answer:
a) I have another column called MatchID and, since I collect these data from many sources, some rows are repeated and I wanted to disconsider them.
In "normal" tables I use, for example,COLUMN1 as Dimension and COUNT(DISTINCT(MatchID)) as Expression but I don't know how to do something similar in this new table.
b) I want to know also the percentage of matches each player was in.
In "normal" tables I simply add an Expression =Count(DISTINCT(MatchID))/Count(Total DISTINCT(MatchID)) but I don't know how to do something similar in this new table.
(I need this percentage only for the Total column)
Any friend can help me to finish this table?
Many thanks in advance
Can you share the file you worked until?
Consider this data:
Column1 | Column2 | Match ID |
---|---|---|
John | Mary | 1 |
Fred | Paul | 2 |
John | Paul | 3 |
Fred | Mary | 4 |
John | Mary | 1 |
Fred | Paul | 2 |
Mary | Paul | 5 |
John | Fred | 6 |
And this script:
Table1:
LOAD Column1,
Column2,
MatchID,
SubField(Column1&'_'&'COLUMN1'&'-'&Column2&'_'&'COLUMN2','-') as COLUMNS
FROM
[MySourceFile.xlsx]
(ooxml, embedded labels, table is [Sheet1]);
LOAD MatchID,
Resident Tab1
Group by MatchID;
This is the table I want to get:
Name | Column1 | Column2 | Sum | Pct |
---|---|---|---|---|
John | 3 | 0 | 3 | 50% |
Mary | 1 | 2 | 3 | 50% |
Paul | 0 | 3 | 3 | 50% |
Fred | 2 | 1 | 3 | 50% |
To avoid any doubts: I want that the table count only once each MatchID; So, all pcts mst be 50%, because there are 6 matches (6 MatchIDs) and each players appears in 3 of them.
Many thanks in advance.