How can I build one table merging infos from two columns?

[mlarruda]

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.

[vishsaggi]

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)

[mlarruda]

Thank you but, since I have many player names, this LOAD * INLINE routine isn't a viable option.

[vishsaggi]

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?

[mlarruda]

OK. I will try. Thanks.

[qliksus]

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)

[mlarruda]

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

[vishsaggi]

Can you share the file you worked until?

[mlarruda]

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.