Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Highlighted
sportsdata
New Contributor

Create a additional column

Hi everyone

i am new to qlikview. I have the following dataset

team, points, matchid

TeamA,34,1001

TeamB,20,1001

TeamC,10,1010

TeamA,15,1010

 

Team A played TeamB indicated by matchid

Team A scored 34 points and TeamB scored 20 points

I need to create a field that indicates Team A conceded 20 points and Team B conceded 34 points

From there I need to identify that TeamA won the match as they scored more points than TeamB

I hope someone can help me

Thanks

 

Labels (1)
2 Replies
MVP
MVP

Re: Create a additional column

This is the code:

Test:
LOAD * Inline
[
team, points, matchid
TeamA,34,1001
TeamB,20,1001
TeamC,10,1010
TeamA,15,1010
];

Join (Test)
LOAD matchid,
	team as teamX,
	points as conceded
Resident Test;

Test2:
LOAD matchid, 
	team,
	points as scored,
	conceded,
	if(points > conceded, 'won', 
		if(points = conceded, 'draw', 'lost')) as result
Resident Test
Where team <> teamX;

Drop Table Test;
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
mdmukramali
Valued Contributor III

Re: Create a additional column

Hi,

 

You can try something like this.

 

Data:
LOAD * Inline
[
team, points, matchid
TeamA,34,1001
TeamB,20,1001
TeamC,10,1010
TeamA,15,1010
]
;

Left Join(Data)
LOAD matchid,
Max(points) as Max_Points,
Min(points) as Min_Points
Resident Data
Group by matchid;


Table:
NoConcatenate
LOAD *,
If(points=Max_Points,Min_Points,Max_Points) as conceded_points
Resident Data;

DROP Table Data;

screenshot.PNG

 

if you have large data set upload sample data to file with more data so that we can provide another alternative solution.

 

Thanks,

Mohammed Mukram