topic Re: Create a additional column in QlikView

Create a additional column

sportsdata — Sat, 16 Nov 2024 04:48:18 GMT

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

Re: Create a additional column

jonathandienst — Thu, 10 Jan 2019 06:20:40 GMT

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;

Re: Create a additional column

mdmukramali — Thu, 10 Jan 2019 06:06:12 GMT

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;

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