Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

calculate text values from column in qlikview

Hi all 

I have this kind of data 

 
 
 

Capture.PNG

 

now i want above data like in this format in qlikview

TPD.SessionsTPD.YearPitStopsSum(TPD.Penalities)
Session120151-39
Session220155-24
Session320156-31

 

this PitStops shows that

if you check Session 1

 
 
 

Untitled.png

for Session 2 there is 5 rows where pitStops is 

 

Untitled.png

and for Session 3 there is 6 

 

Untitled.png

 

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

 

1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

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;
		

 

View solution in original post

3 Replies
Highlighted
Specialist
Specialist

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;
		

 

View solution in original post

Highlighted
Contributor III
Contributor III

@vamsee what columns should i add in table />

Highlighted
Specialist
Specialist

Session, Year, PitStops can be added as a dimension

Expression: Laps, Penalty