QV scripting join,exists or other solution ?

[#1]

Hi Everybody !

I need to do something in QV Scripting because of restriction with database.

I created this sample data to explain my issue.

I'm searching for an elegant way to detect teams that lost a game and then win one. (In my example, only SG is supposed to match this condition)

Thanks a lot for your help !

SAMPLE_DATA:

Load * inline

[

RESULT,DATE_RESULT,TEAM

WIN,01/01/2016,SG

LOOSE,02/01/2016,SG

WIN,03/01/2016,SG

NUL,04/01/2016,SG

WIN,01/01/2016,EAL

LOOSE,02/01/2016,EAL

NUL,04/01/2016,EAL

LOOSE,06/01/2016,SEA

LOOSE,07/01/2016,SEA

LOOSE,02/01/2016,SEA

NUL,03/01/2016,SEA

LOOSE,05/01/2016,SEA

LOOSE,06/01/2016,LLE

LOOSE,07/01/2016,LLE

];

[luciancotea]

Well, first of all is LOSE, not LOOSE. Use peek() to check the previous record.

LOAD *, if( peek('RESULT') = 'LOSE' and RESULT = 'WIN', 'We have a match', '') as CheckPoint

RESIDENT SAMPLE_DATA;

[Gysbert_Wassenaar]

Perhaps like this:

SAMPLE_DATA:

Load *,

     If(Previous(TEAM)=TEAM and RESULT='WIN' and Previous(RESULT)='LOOSE',1,0) as WinAfterLoose

INLINE [

RESULT,DATE_RESULT,TEAM

WIN,01/01/2016,SG

LOOSE,02/01/2016,SG

WIN,03/01/2016,SG

NUL,04/01/2016,SG

WIN,01/01/2016,EAL

LOOSE,02/01/2016,EAL

NUL,04/01/2016,EAL

LOOSE,06/01/2016,SEA

LOOSE,07/01/2016,SEA

LOOSE,02/01/2016,SEA

NUL,03/01/2016,SEA

LOOSE,05/01/2016,SEA

LOOSE,06/01/2016,LLE

LOOSE,07/01/2016,LLE

];

Or perhaps

SAMPLE_DATA:

Load *,

     RESULT & '|' & TEAM as KEY,

     If(RESULT='WIN' and Exists(KEY, 'LOOSE' & '|' & TEAM),1,0) as WinAfterAnyLoose

INLINE [

RESULT,DATE_RESULT,TEAM

WIN,01/01/2016,SG

LOOSE,02/01/2016,SG

WIN,03/01/2016,SG

NUL,04/01/2016,SG

WIN,01/01/2016,EAL

LOOSE,02/01/2016,EAL

NUL,04/01/2016,EAL

LOOSE,06/01/2016,SEA

LOOSE,07/01/2016,SEA

LOOSE,02/01/2016,SEA

NUL,03/01/2016,SEA

LOOSE,05/01/2016,SEA

LOOSE,06/01/2016,LLE

LOOSE,07/01/2016,LLE

];

[#4]

Hi Lucian, thanks a lot for your help and sorry about my English . ( I'm currently working on it !).

I'm gonna post a reply to both Gysbert & you ;

[#5]

Hi Gysbert, thanks a lot for your help !

I wasn't enough explicit, your second solution works (Lucian's solution too), but there is no comparaison between date, here it works thanks to the sort of data .

Should I : make something to sort data according to the date, or add a condition on LOSE DATE < WIN DATE ?

Thanks again to both of your for your precious (and very fast!) advise.

[luciancotea]

Yes, sorting is important. Also checking if is the same team on the previous row (see Gysbert's code)

[Gysbert_Wassenaar · Accepted solution]

Your data needs to be sorted so load it first in a temporary table and the use a resident load to sort the data:

Temp:

LOAD *  FROM:

Result

LOAD *, ...calculations_here...

RESIDENT Temp

Order By TEAM, DATE;

DROP TABLE Temp;

[#8]

Thanks a lot guys, By concatenatining all information you provided, I'm now able to solve my problematic !