Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
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?

Tags (1)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Basic Qlikview Development Question

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

Re: Basic Qlikview Development Question

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?

MVP & Luminary
MVP & Luminary

Re: Basic Qlikview Development Question

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

Re: Basic Qlikview Development Question

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.

MVP & Luminary
MVP & Luminary

Re: Basic Qlikview Development Question

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

Not applicable

Re: Basic Qlikview Development Question

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.

MVP & Luminary
MVP & Luminary

Re: Basic Qlikview Development Question

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