Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

leongwaimeng
New Contributor II

Calculating and grouping values from two identical column

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)

HomeTeamAwayTeamFTHGFTAGFTR
BurnleySwansea01A
Crystal PalaceWest Brom01A
EvertonTottenham11D
HullLeicester21H
Man CitySunderland21H
MiddlesbroughStoke11D
SouthamptonWatford11D
ArsenalLiverpool34A
BournemouthMan United13A
ChelseaWest Ham21H
Man UnitedSouthampton20H
BurnleyLiverpool20H
LeicesterArsenal00D
StokeMan City14A
SwanseaHull02A
TottenhamCrystal Palace10H
WatfordChelsea12A
West BromEverton12A
SunderlandMiddlesbrough12A
West HamBournemouth10H

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

1 Solution

Accepted Solutions

Re: Calculating and grouping values from two identical column

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

Capture.JPG

12 Replies

Re: Calculating and grouping values from two identical column

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

Capture.JPG

vinieme12
Esteemed Contributor II

Re: Calculating and grouping values from two identical column

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))

leongwaimeng
New Contributor II

Re: Calculating and grouping values from two identical column

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!

Re: Calculating and grouping values from two identical column

So as per your logic for Swansea, there should 1 win and 1 loose, right?

leongwaimeng
New Contributor II

Re: Calculating and grouping values from two identical column

Yes correct.

(Strangely, if you look at your table, it's correct for Arsenal.)

vinieme12
Esteemed Contributor II

Re: Calculating and grouping values from two identical column

Have you tried my approach?

Re: Calculating and grouping values from two identical column

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

Capture.JPG

leongwaimeng
New Contributor II

Re: Calculating and grouping values from two identical column

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!

leongwaimeng
New Contributor II

Re: Calculating and grouping values from two identical column

Hey Kushal,

It works now. Great! Thanks so much for your help!

Community Browser