How to omit a previous row from a table based off a string in the next row.

[AyCe1082]

Hi guys,

I'm numbering rows in a table and I need to remove some rows before they are numbered.

In the image, I need to remove 'Shot' and 'Aerial Duel Won' because the next event is a goal ,otherwise they are counted twice in the charts I need them for. There will be other events like them that would also need to be removed. So I think previous can be anything if the next or current = 'Goal' but I am a bit stuck figuring out how to not include that previous event as the script is being loaded.

I appreciate any help.

[Saravanan_Desingh · Accepted solution]

One solution is.

tab1:
LOAD RecNo() As RowID, * INLINE [
    ShotType
    Shot
    Shot
    Shot
    Shot
    Free Kick
    Shot
    Shot
    Shot
    Shot
    Shot
    Shot
    Shot
    Goal
    Free Kick
    Aerial Duel Won
    Goal
    Shot
];

tab2:
LOAD *, If(Peek(ShotType)='Goal','N','Y') As ShotFlag
Resident tab1
Order By RowID desc 
;

tab3:
NoConcatenate
LOAD *, RowNo() As ShotRank
Resident tab2
Where ShotFlag='Y'
Order By RowID
;

Drop Table tab1, tab2;

[Saravanan_Desingh]

[AyCe1082]

The data will be different every time as this is an ongoing automated process. I am not sure an inline will work here. Also it involves two different teams which is why I have two shotrank columns and they are the numerical order in which they occurred in the match so I presume the rows that have to be removed must happen before they are ranked.

This is what I have used to create the table.

Shots:
LOAD *,
if(HomeAwayshots = '1',Sequence) as ShotRank,
if(HomeAwayshots = '2',Sequence) as ShotRank2;
LOAD
HomeAwayshots,
If(Previous(HomeAwayshots) = HomeAwayshots, Rangesum(1, peek('Sequence')), 1) as Sequence,
Player as ShotPlayer,     
Teamnameshots, 
Half as PeriodHalf,
Event_Time,
Event_EventName as ShotType

Resident Shotspre 
order by HomeAwayshots,Half, Event_Time asc
;

drop table Shotspre;

[AyCe1082]

I managed a solution using saran7de's solution combined with what I was already using.

The two ShotRank fields were retained while omitting the previous row to the Goals.

Shotspre2:
LOAD
RecNo() As RowID,
HomeAwayshots,
Player as ShotPlayer,     
Teamnameshots, 
Half as PeriodHalf,
Event_Time,
Event_EventName as ShotType

Resident Shotspre 
order by HomeAwayshots,Half, Event_Time asc
;

drop table Shotspre;


Shotspre3:
LOAD *, If(Peek(ShotType)='Goal','N','Y') As ShotFlag
Resident Shotspre2
Order By RowID desc 
;

Shots:
LOAD *,
if(HomeAwayshots = '1',Sequence) as ShotRank,
if(HomeAwayshots = '2',Sequence) as ShotRank2;
LOAD *,
If(Previous(HomeAwayshots) = HomeAwayshots, Rangesum(1, peek('Sequence')), 1) as Sequence;
NoConcatenate
LOAD *, RowNo() As ShotRankpre
Resident Shotspre3
Where ShotFlag='Y'
Order By RowID
;

Drop fields ShotFlag, Sequence, ShotRankpre from Shots;
Drop Table Shotspre2, Shotspre3;

Thanks for the assistance.