Calculating and grouping values from two identical column

[Anonymous]

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

[vinieme12]

I'd suggest a slight change as the total counts are coming incorrect , total matches played in the league should be 20 not 40.

GAME_FACT:

LOAD Rowno() as MatchID,

  HomeTeam,

    AwayTeam,

    FTHG,

    FTAG,

    FTR

FROM

[https://community.qlik.com/thread/264675]

(html, codepage is 1252, embedded labels, table is @1);

TeamsTab:

LOAD MatchID,

  HomeTeam as TEAM,

'Home' as GameFlag ,

  Pick(Match(FTR,'A','H','D'),'L','W','D') as MatchResult

resident GAME_FACT;

Concatenate

LOAD MatchID,

    AwayTeam as TEAM,

    'Away' as GameFlag,

      Pick(Match(FTR,'A','H','D'),'W','L','D') as MatchResult

resident GAME_FACT;

Check the app for expressions

[vinieme12]

the only difference is the way you count the Games Played, instead of count(TEAM) , you really need to be counting distinct games played which is why you need a MatchID.

But your choice!!

[Anonymous]

Thanks alot for this Vineeth!