calculate text values from column in qlikview

[capriconuser]

Hi all 

I have this kind of data 

now i want above data like in this format in qlikview

TPD.Sessions	TPD.Year	PitStops	Sum(TPD.Penalities)
Session1	2015	1	-39
Session2	2015	5	-24
Session3	2015	6	-31

this PitStops shows that

if you check Session 1

for Session 2 there is 5 rows where pitStops is 

and for Session 3 there is 6 

so how to calculate PitStops aginst each session and sum up on to one value .. any idea ?>

[vamsee · Accepted solution]

Try 

Source:
Load 
		Sessions,
		Year,
		Sessions&Year as Key,
		IF(Len(Trim(Pitstop1))>0,1,IF(Len(Trim(Pitstop2))>0,1, If(Len(Trim(Pitstop3))>0,1, 0))) as Flag,
		Pitstop1,
		Pitstop2,
		Pitstop3,
		Laps,
		Penalty
From Source;


Left Join(Source)
Load
		Key,
		Sum(Flag) as PitStops
Resident Source
Group By
		Key
;				
Drop Fields Key, Flag;
		

[capriconuser]

@vamsee what columns should i add in table />

[vamsee]

Session, Year, PitStops can be added as a dimension

Expression: Laps, Penalty