Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Below is a sample table of my data and I would like to an overall table where it calculates overall number of wins, overall number of draws and overall number of losses for each team (note that the list in HomeTeam and AwayTeam are the same)
HomeTeam | AwayTeam | FTHG | FTAG | FTR |
Burnley | Swansea | 0 | 1 | A |
Crystal Palace | West Brom | 0 | 1 | A |
Everton | Tottenham | 1 | 1 | D |
Hull | Leicester | 2 | 1 | H |
Man City | Sunderland | 2 | 1 | H |
Middlesbrough | Stoke | 1 | 1 | D |
Southampton | Watford | 1 | 1 | D |
Arsenal | Liverpool | 3 | 4 | A |
Bournemouth | Man United | 1 | 3 | A |
Chelsea | West Ham | 2 | 1 | H |
Man United | Southampton | 2 | 0 | H |
Burnley | Liverpool | 2 | 0 | H |
Leicester | Arsenal | 0 | 0 | D |
Stoke | Man City | 1 | 4 | A |
Swansea | Hull | 0 | 2 | A |
Tottenham | Crystal Palace | 1 | 0 | H |
Watford | Chelsea | 1 | 2 | A |
West Brom | Everton | 1 | 2 | A |
Sunderland | Middlesbrough | 1 | 2 | A |
West Ham | Bournemouth | 1 | 0 | H |
What i have done
Picking the values separately from HomeTeam and AwayTeam is simple. What i am unable to do is to combine the records for HomeTeam and AwayTeam so that we have just 20 teams and the record will show for example:
Team | Played | W | D | L |
Burnley | 2 | 1 | 0 | 1 |
Swansea | 2 | 0 | 0 | 2 |
....
Any help is much appreciated!
Regards,
Wai Meng
Data:
LOAD HomeTeam,
AwayTeam,
FTHG,
FTAG,
FTR
FROM
[https://community.qlik.com/thread/264675]
(html, codepage is 1252, embedded labels, table is @1);
New:
LOAD HomeTeam as Team,
FTHG,
FTAG,
FTR,
'HomeTeam' as Flag
Resident Data;
Concatenate(New)
LOAD AwayTeam as Team,
FTHG,
FTAG,
FTR,
'AwayTeam' as Flag
Resident Data;
DROP Table Data;
Final:
NoConcatenate
LOAD *,
if(Flag='HomeTeam' and FTR='H','W',
if(Flag='AwayTeam' and FTR='A','W',
if(FTR='D','D','L'))) as WLD
Resident New;
DROP Table New;
Create straight table
Dimension:
Team
Expression:
=Count(Team) -> Played
=Count({<WLD={'W'}>}Team) ->W
=Count({<WLD={'L'}>}Team) ->L
=Count({<WLD={'D'}>}Team) ->D
Data:
LOAD HomeTeam,
AwayTeam,
FTHG,
FTAG,
FTR
FROM
[https://community.qlik.com/thread/264675]
(html, codepage is 1252, embedded labels, table is @1);
New:
LOAD HomeTeam as Team,
FTHG,
FTAG,
FTR,
'HomeTeam' as Flag
Resident Data;
Concatenate(New)
LOAD AwayTeam as Team,
FTHG,
FTAG,
FTR,
'AwayTeam' as Flag
Resident Data;
DROP Table Data;
Create straight table
Dimension:
Team
Expression:
=Count(Team) -> Played
=Count({<FTR={'H'}>}Team) ->W
=Count({<FTR={'A'}>}Team) ->L
=Count({<FTR={'D'}>}Team) ->D
if you need a more detailed breakdown like bifurcating away and home , wins and loses, try as below
HOME_GAMES:
LOAD * INLINE [
HomeTeam,FTHG,FTRH
Burnley,0,A
Crystal Palace,0,A
Everton,1,D
Hull,2,H
Man City,2,H
Middlesbrough,1,D
Southampton,1,D
Arsenal,3,A
Bournemouth,1,A
Chelsea,2,H
Man United,2,H
Burnley,2,H
Leicester,0,D
Stoke,1,A
Swansea,0,A
Tottenham,1,H
Watford,1,A
West Brom,1,A
Sunderland,1,A
West Ham,1,H
];
AWAY_GAMES:
LOAD * INLINE [
AwayTeam,FTAG, FTRA
Swansea,1, A
West Brom,1, A
Tottenham,1, D
Leicester,1, H
Sunderland,1, H
Stoke,1, D
Watford,1, D
Liverpool,4, A
Man United,3, A
West Ham,1, H
Southampton,0, H
Liverpool,0, H
Arsenal,0, D
Man City,4, A
Hull,2, A
Crystal Palace,0, H
Chelsea,2, A
Everton,2, A
Middlesbrough,2, A
Bournemouth,0, H
];
ALL_TEAMS:
LOAD Distinct HomeTeam as ALL_TEAMS,
HomeTeam as HomeTeam,
HomeTeam as AwayTeam
RESIDENT HOME_GAMES;
Home Wins
count({<FTRH={'A'}>}FTRH)
Home Draws
count({<FTRH={'D'}>}FTRH)
Home Loses
count({<FTRH={'H'}>}FTRH)
Away Wins
count({<FTRA={'A'}>}FTRA)
Away Draws
count({<FTRA={'D'}>}FTRA)
Away Loses
count({<FTRA={'H'}>}FTRA)
Total Wins
rangesum(count({<FTRH={'A'}>}FTRH),count({<FTRA={'A'}>}FTRA))
Total Draws
rangesum(count({<FTRH={'D'}>}FTRH),count({<FTRA={'D'}>}FTRA))
Total Losses
rangesum(count({<FTRH={'H'}>}FTRH),count({<FTRA={'H'}>}FTRA))
Thanks Kushal and Vineeth for helping out.
Unfortunately, i gave it a go and Kushal, i think there is a slight error somewhere!
Based on your script, each team still counts on H as a win when in fact, in some games where the team plays away, FTR ='A' should be credited as a win for the team.
Right now, a WIN for a team is still measured only by FTR='H'.
Sorry for the bother!
So as per your logic for Swansea, there should 1 win and 1 loose, right?
Yes correct.
(Strangely, if you look at your table, it's correct for Arsenal.)
Have you tried my approach?
Data:
LOAD HomeTeam,
AwayTeam,
FTHG,
FTAG,
FTR
FROM
[https://community.qlik.com/thread/264675]
(html, codepage is 1252, embedded labels, table is @1);
New:
LOAD HomeTeam as Team,
FTHG,
FTAG,
FTR,
'HomeTeam' as Flag
Resident Data;
Concatenate(New)
LOAD AwayTeam as Team,
FTHG,
FTAG,
FTR,
'AwayTeam' as Flag
Resident Data;
DROP Table Data;
Final:
NoConcatenate
LOAD *,
if(Flag='HomeTeam' and FTR='H','W',
if(Flag='AwayTeam' and FTR='A','W',
if(FTR='D','D','L'))) as WLD
Resident New;
DROP Table New;
Create straight table
Dimension:
Team
Expression:
=Count(Team) -> Played
=Count({<WLD={'W'}>}Team) ->W
=Count({<WLD={'L'}>}Team) ->L
=Count({<WLD={'D'}>}Team) ->D
Hi Vineeth,
Thanks for your approach. It will probably work but it's not feasible in the long run as the games run up to 38 games for each team and there are several league seasons to add to the data. I appreciate your help though, thank you!
Hey Kushal,
It works now. Great! Thanks so much for your help!