topic Sum goals a team has suffered in QlikView

Sum goals a team has suffered

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Sum goals a team has suffered

tresB — Tue, 17 Jan 2017 09:49:20 GMT

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';

Re: Sum goals a team has suffered

MK_QSL — Tue, 17 Jan 2017 09:53:21 GMT

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;

Re: Sum goals a team has suffered

swuehl — Tue, 17 Jan 2017 10:23:22 GMT

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;

Re: Sum goals a team has suffered

MK_QSL — Tue, 17 Jan 2017 10:26:31 GMT

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

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

Re: Sum goals a team has suffered

Tue, 17 Jan 2017 10:40:53 GMT

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?

Re: Sum goals a team has suffered

tresB — Tue, 17 Jan 2017 10:52:20 GMT

May be like in attached.

Re: Sum goals a team has suffered

Tue, 17 Jan 2017 11:29:06 GMT

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

Re: Sum goals a team has suffered

swuehl — Tue, 17 Jan 2017 11:35:30 GMT

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;

Re: Sum goals a team has suffered

Wed, 18 Jan 2017 09:07:25 GMT

Thank you Stefan!