Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?>
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;
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;
@vamsee what columns should i add in table />
Session, Year, PitStops can be added as a dimension
Expression: Laps, Penalty