topic Re: Basic Qlikview Development Question in QlikView

Basic Qlikview Development Question

Sun, 01 Nov 2015 14:40:02 GMT

I haven't used QV in a while and I'm struggling with something I'm sure is pretty basic;

I have a lot of data about sports results in excel. I have loaded it into Qlikview and have begun analysing the data. I have an issue I need some help with. I have the following fields;

Home Goals; Home Team; Away Team; Away Goals; Result; Home Points; Away Points;

I have two issues; Because the teams are split by Home Team and Away Team, I cannot assign points to a specific team, e.g. if game 1 is 4-0 to the home team, result is Home Win, Home Points are 3, Away points 0. The 3 points gets assigned to both the Home Team and the Away Team. How do I link points to the specific team that won within a Qlikview chat (table/Pivot table)?

Really I need a list of Teams, but how do I do this when the data splits the teams between home and away team?

Re: Basic Qlikview Development Question

Sun, 01 Nov 2015 18:01:31 GMT

Since posting this I have been able to use the RecNo() and QUALIFY/UNQUALIFY functions to get a list of all teams, so part of my issue has been resolved. I now need to understand how I assign 3 points to the correct team for a win. Any thoughts on how best to do this?

Re: Basic Qlikview Development Question

Gysbert_Wassenaar — Sun, 01 Nov 2015 18:09:42 GMT

I would load the data from the excel file in two steps and concatenate the results in one table:

Data:

LOAD

"Home Goals" as Goals,

"Home Team" as Team,

"Result",

"Home Points" as Points

'Yes' as "IsHomeTeam"

FROM myexcel.xlsx (ooxml ...etc) ;

Concatenate(Data)

LOAD

"Away Goals" as Goals,

"AwayTeam" as Team,

"Result",

"Away Points" as Points

'No' as "IsHomeTeam"

FROM myexcel.xlsx (ooxml ...etc) ;

Re: Basic Qlikview Development Question

Sun, 01 Nov 2015 18:36:53 GMT

Gysbert,

Many thanks for the response, your answer solves the points issue perfectly.

Is it possible to keep the individual Home and Away teams as part of this calculation? The reason I ask is I now have the full team list, but I cannot see the teams that the points were achieved against. In other words I see a list of teams and their points but not the results of the individual matches.

Re: Basic Qlikview Development Question

Gysbert_Wassenaar — Mon, 02 Nov 2015 11:08:29 GMT

Maybe like this:

Data:

LOAD

"Home Goals" as Goals,

"Home Team" as Team,

"Away Team" as Opposition,

"Result",

"Home Points" as Points

'Yes' as "IsHomeTeam"

FROM myexcel.xlsx (ooxml ...etc) ;

Concatenate(Data)

LOAD

"Away Goals" as Goals,

"Away Team" as Team,

"Home Team" as Opposition,

"Result",

"Away Points" as Points

'No' as "IsHomeTeam"

FROM myexcel.xlsx (ooxml ...etc) ;

Re: Basic Qlikview Development Question

Tue, 03 Nov 2015 09:16:04 GMT

Thank you so much for your help, this has worked perfectly and I've already learnt a lot of new functions!

I hope there is one more thing you might help me with on this. The suggestion above works perfectly for 1 Data Set i.e. Current Season. How do I code this to include 5 more data sets for the previous seasons?

They are 5 separate sheets on the same excel document.

Re: Basic Qlikview Development Question

Gysbert_Wassenaar — Tue, 03 Nov 2015 09:57:29 GMT

Load them the same way, but include the season for each season by adding a new Season field and filling it with the appropriate values.