Column split/join?

[#1]

Hi.

I'm working with some soccer-analytics to provide support and knowledge when betting. But I have met some problems. All of my data comes from one single Excel-document just to be clear.

The problem.

I have one attribute "Club Name" that is used in the Table-file in Excel and it works fine. The problem is when I try to load the fixtures where there is one home team and one away team. I can't name the two columns "Club Name" since they are the same.

The problem is when I for example have one [Club Name] marked but the fixtures doesn't respond to this since it's named Home or Away and not Club Name. Is there some way to fix and say that a [Club Name] is EITHER HOME team or the AWAY team? So when I for example mark "Charlton" all the Charlton-games will appear in the fixtures? Thank you.

[markodonovan]

Hi Sebastian,

If you loaded the main table as so:

QUALIFY *;
UNQUALIFY recordno;
premier_results:
LOAD
RecNo() as recordno,
Season,
Matchday,
[Home Team],
[Away Team],
[Goals Home],
[Goals Away]
FROM
premier_league_2011.xls
(biff, embedded labels, header is 2 lines, table is Results$);

You could create a table with the Club (Team in this example)  loaded twice to get the Home\Away teams into the same field.

QUALIFY *;
UNQUALIFY Team,recordno;
Linktogames:
LOAD
[premier_results.Home Team] as Team,
recordno
resident premier_results;

QUALIFY *;
UNQUALIFY Team,recordno;
LOAD
[premier_results.Away Team] as Team,
recordno
resident premier_results;

This will create a table called Linktogames.

Hope this helps.

Mark

www.techstuffy.com

[JonnyPoole]

For sure. Its a data model thing.

In the sample below i transformed data where i have 1 row per game into a table where i have 1 row per team/Game combination. I use a GAMEID to keep track of the games and then join a list of home teams and a list of away teams to that main table on GAMEID

Here is the sample data

and here is the modelled data

here is the load script:

GameScores:

LOAD

  recno() as GameID,

  Home,

     Away,

     [Home Score],

     [Away Score],

     Week

FROM

(ooxml, embedded labels, table is Sheet1);

//load homes

TeamScore:

load

  GameID,

  Home as Team,

  1 as HomeTeamFlag,

  0 as AwayTeamFlag,

  [Home Score] as [Score],

  Week

resident GameScores;

Concatenate (TeamScore)

load

  GameID,

  Away as Team,

  0 as HomeTeamFlag,

  1 as AwayTeamFlag,

  [Away Score] as [Score],

  Week

resident GameScores;

drop table GameScores;

HomeTeams:

load

  GameID,

  Team as HomeTeam

resident TeamScore

where HomeTeamFlag=1;

AwayTeams:

load

  GameID,

  Team as AwayTeam

resident TeamScore

where AwayTeamFlag=1;

[markodonovan]

Hi Sebastian,

If you get time have a look a couple of videos I have done on analysis of premier league football results.

Practical QlikView Training Course - 35/49 - Football Scores - Part 1 - YouTube

Practical QlikView Training Course - 36/49 - Football Scores - Part 2 - YouTube

Thanks

Mark

http://www.techstuffybooks.com