Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Viewing data as a sequence

Hi All

I have been struggling with the problem for a couple of weeks now

I am a novice at Qlikview and just unable to get this right

I need to work out the top sequence of Pitch Type for the 1st 3 Pitches

I have attached a sample data file

I use Pitch Count to determine order of Pitches

1--0 or 0--1 = 1st Pitch

1--1,2--0,0--2 = 2nd Pitch

Strikeout,3--0,2--1,1--2 = 3rd Pitch

However, the Pitch count column is empty when the Batter makes contact with the ball. As a result, Pitch Result has a value 'Ball in Play'

So when this happens, Pitch Count is empty and could equal 1st, 2nd or 3rd pitch

I need to determine the most common Pitch Type sequence for the 1st 3 pitches (i.e Fast Ball, Change Up, Screw Ball)

Please could someone help as this is a really important stat for Baseball and Softball

1 Solution

Accepted Solutions
MVP
MVP

Re: Viewing data as a sequence

Still unsure about the required logic since I don't really know baseball.

Maybe like

LOAD *,

  if([Game Pitch Result] = 'Ball in Play',

          Rangesum(Peek(Pitch),1),

          Rangesum(SUBFIELD([Pitch Count],'--',1),SUBFIELD([Pitch Count],'--',2))

     ) as Pitch,

  RowNo() as RowNo

;

LOAD [Pitchers Name],

     [Pitch Type],

     [Game Pitch Result],

     [Pitch Count],

     Period

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

View solution in original post

8 Replies
MVP
MVP

Re: Viewing data as a sequence

I haven't really understood what you want to do with the 'Ball in play' case (there are also other cases, like 'Walk' or 'Hit by pitch' in [Pitch Count] field.)

You can start with this

LOAD *, RANGESUM(SUBFIELD([Pitch Count],'--',1),SUBFIELD([Pitch Count],'--',2)) as Pitch;

LOAD [Pitchers Name],

     [Pitch Type],

     [Game Pitch Result],

     [Pitch Count],

     Period

FROM

[Sample Data.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Then create a straight table chart with Pitch as dimension, and

=Mode([Pitch Type])

as expression.

Pitch Mode([Pitch Type])
Change Up
1Screw Ball
2Change Up
3Curve Ball
5Drop Ball
6Curve Ball
Not applicable

Re: Viewing data as a sequence

Hi

Thank you, the expression works

However, when the Pitch Result column has the value ''Ball in Play'', the expression does not take into account the Pitch Type as this Pitch Count column is empty and hence show 1 less pitch

MVP
MVP

Re: Viewing data as a sequence

Still unsure about the required logic since I don't really know baseball.

Maybe like

LOAD *,

  if([Game Pitch Result] = 'Ball in Play',

          Rangesum(Peek(Pitch),1),

          Rangesum(SUBFIELD([Pitch Count],'--',1),SUBFIELD([Pitch Count],'--',2))

     ) as Pitch,

  RowNo() as RowNo

;

LOAD [Pitchers Name],

     [Pitch Type],

     [Game Pitch Result],

     [Pitch Count],

     Period

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

View solution in original post

Not applicable

Re: Viewing data as a sequence

Great. It works

Thank you

oknotsen
Honored Contributor III

Re: Viewing data as a sequence

If your question is now answered, please flag the correct answer with Correct Answer and optionally any Helpful replies.

If not, please let us know what part of your question still needs help .

May you live in interesting times!
Not applicable

Re: Viewing data as a sequence

Hi Onno

I cannot see the Correct Answer icon on my PC. I have used multiple browsers and still cannot locate it

Re: Viewing data as a sequence

another way to sum the subfields could be:

Evaluate(Replace([Pitch Count],'--','+'))

hope this helps

regards

Marco

MVP
MVP

Re: Viewing data as a sequence

You may need to access the thread not from the inbox, but by following the link e.g. by clicking on the title.