Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Basic Qlikview Development Question

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?

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

6 Replies
Not applicable
Author

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?

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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.

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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.

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand