Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
sportsdata
Contributor
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
jonathandienst
Partner - Champion III
Partner - Champion III

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
Specialist III
Specialist III

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