topic Re: Column split/join? in QlikView

Column split/join?

Wed, 29 Oct 2014 20:23:01 GMT

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.

Re: Column split/join?

markodonovan — Wed, 29 Oct 2014 20:40:11 GMT

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

Re: Column split/join?

JonnyPoole — Wed, 29 Oct 2014 20:47:10 GMT

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;

Re: Column split/join?

markodonovan — Wed, 29 Oct 2014 21:35:37 GMT

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