Basic Qlikview Development Question

[#1]

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?

[#2]

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

[#4]

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 · Accepted solution]

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

[#6]

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.