topic Re: How to omit a previous row from a table based off a string in the next row. in QlikView

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

AyCe1082 — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

Saravanan_Desingh — Fri, 05 Jun 2020 19:56:09 GMT

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;

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

Saravanan_Desingh — Fri, 05 Jun 2020 19:56:54 GMT

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

AyCe1082 — Sat, 06 Jun 2020 11:55:16 GMT

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;

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

AyCe1082 — Sun, 07 Jun 2020 15:40:23 GMT

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.