Sum goals a team has suffered

[#1]

Hi,

this is a sample of  my scheme:

MatchID	Team	Place	Goals
1	Cesena	Home	2
2	Bari	Home	4
3	Livorno	Home	4
1	Brescia	Away	0
2	Spezia	Away	3
3	Pescara	Away	0

You can see all the data in the attached file.

My problem is that i can easilly sum per team all goals that it have achieve, but i can't find a way to sum all the goals a team has suffered. For example, Bari, with MatchID=2, put 4 goals and suffered 3. Do you have any suggestion on that?

Thank you in advance

[tresesco]

I guess just a self join would do the job for you. Try like:

LOAD MatchID,

     Team,

     //Place,

     Goals

FROM

[I2 matches.xlsx]

(ooxml, embedded labels, table is Match) where Place='Home';

Left Join

LOAD MatchID,

     Team as Opponent,

     //Place,

     Goals as GoalSuffered

FROM

[I2 matches.xlsx]

(ooxml, embedded labels, table is Match) where Place='Away';

[MK_QSL]

Use enclosed file..

Data:
LOAD 
  MatchID, 
  Team, 
  Place, 
  Goals
FROM
Community_246442.xlsx
(ooxml, embedded labels, table is Match);


NoConcatenate
Match:
Load MatchID, Team as Team1, Goals as GoalMade Resident Data Where Place ='Away';
Left Join
Load MatchID, Team as Team2, Goals as GoalGiven Resident Data Where Place ='Home';


Drop Table Data;


Teams:
Load MatchID, Team1 as Team Resident Match;
Load MatchID, Team2 as Team Resident Match;

[swuehl]

A different approach with different results, also taking into account the suffered goals for away teams:

Data:
LOAD
  MatchID,
  Team,
  Place,
  Goals
FROM
Community_246442.xlsx
(ooxml, embedded labels, table is Match);


JOIN
LOAD MatchID,
  Team as TeamCheck,
  Goals as GoalsSuffered
Resident Data;


Final:
LOAD MatchID,
  Team,
  Place,
  Goals,
  GoalsSuffered
Resident Data
WHERE Team <> TeamCheck;


DROP TABLE Data;

[MK_QSL]

Yes, you are right.. I have given half solution...

In fact, it's not half solution.. it's wrong solution..

[#6]

Thanks a lot for your answers!

I will have to study to fully understand how you achieve that, so your answers are really helpful.

Now, one more question: Based on goals made, someone can find the result of the match. So i can say that for MatchID=1 Cesena won Brescia because Home goals=2 and Away goals=0.

How can i count how many victories, draws and defeats a team has? Should i do a calculation on expression?

[tresesco]

May be like in attached.

[#8]

Tresesco thanks for your answer, but i have personal edition and i cannot open it...

[swuehl · Accepted solution]

Maybe like

Data:
LOAD 
  MatchID, 
  Team, 
  Place, 
  Goals
FROM
Community_246442.xlsx
(ooxml, embedded labels, table is Match);


JOIN 
LOAD MatchID,
  Team as TeamCheck,
  Goals as GoalsSuffered
Resident Data;


Final:
LOAD MatchID,
  Team,
  Place,
  Goals,
  GoalsSuffered,
  If(Goals > GoalsSuffered, 'Won', If(Goals = GoalsSuffered,'Tie','Lost')) as Result,
  If(Goals > GoalsSuffered, 3, If(Goals = GoalsSuffered,1,0)) as Points
Resident Data 
WHERE Team <> TeamCheck;


DROP TABLE Data;

[#10]

Thank you Stefan!