topic Re: QV scripting join,exists or other solution ? in QlikView

QV scripting join,exists or other solution ?

Thu, 10 Mar 2016 09:30:40 GMT

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

];

Re: QV scripting join,exists or other solution ?

luciancotea — Thu, 10 Mar 2016 09:40:37 GMT

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;

Re: QV scripting join,exists or other solution ?

Gysbert_Wassenaar — Thu, 10 Mar 2016 09:47:01 GMT

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

];

Re: QV scripting join,exists or other solution ?

Thu, 10 Mar 2016 10:14:45 GMT

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 ;

Re: QV scripting join,exists or other solution ?

Thu, 10 Mar 2016 10:22:22 GMT

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.

Re: QV scripting join,exists or other solution ?

luciancotea — Thu, 10 Mar 2016 10:57:26 GMT

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

Re: QV scripting join,exists or other solution ?

Gysbert_Wassenaar — Thu, 10 Mar 2016 10:59:13 GMT

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;

Re: QV scripting join,exists or other solution ?

Tue, 15 Mar 2016 08:04:25 GMT

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