Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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 II

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

 

Community Browser