Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Sum goals a team has suffered

Hi,

this is a sample of  my scheme:

 

MatchIDTeamPlaceGoals
1CesenaHome2
2BariHome4
3LivornoHome4
1BresciaAway0
2SpeziaAway3
3PescaraAway0

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Sum goals a team has suffered

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;

2017-01-17 12_35_02-QlikView x64 - [C__Users_s.wuehl_Downloads_Community_246442_3.qvw].png

9 Replies
MVP
MVP

Re: Sum goals a team has suffered

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

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;

MVP
MVP

Re: Sum goals a team has suffered

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;

2017-01-17 11_22_23-QlikView x64 - [C__Users_s.wuehl_Downloads_Community_246442_2.qvw].png

Re: Sum goals a team has suffered

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

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

Not applicable

Re: Sum goals a team has suffered

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?

MVP
MVP

Re: Sum goals a team has suffered

May be like in attached.

Not applicable

Re: Sum goals a team has suffered

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

MVP
MVP

Re: Sum goals a team has suffered

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;

2017-01-17 12_35_02-QlikView x64 - [C__Users_s.wuehl_Downloads_Community_246442_3.qvw].png

Not applicable

Re: Sum goals a team has suffered

Thank you Stefan!

Community Browser