Display selected rows and the x number of rows that follow

[ciaran_mcgowan]

Hi all,

I'm building an document that reads information from thousands of HTML files and loads them into Qlikview but I need to find a way to either (a) show the below using selectors or (b) load the data into the document described below.

The information that is loaded is the UserID, an InteractionID, their Score, the InteractionTime like this:

USERID	INTERACTIONID	SCORE	TIME
021	1	15	09:00
021	2	17	09:30
021	3	12	10:45
021	4	15	11:30
043	1	7	8:30
043	2	19	9:45
043	3	17	10:15
043	4	14	12:30
043	5	11	13:50

What I need is to only display Scores >= 17 but I also need to be able to see the score that follows it, even if it's less than 17 (even if it's below 17). So the above table should be shown as:

USERID	INTERACTIONID	SCORE	TIME
021	2	17	09:30
021	3	12	10:45
043	2	19	9:45
043	3	17	10:15
043	4	14	12:30

I'm not sure if it's possible using selectors and I've had no luck doing it via the load script. Any suggestions would be greatly appreciated.

[swuehl]

You can flag the records in your script like

INPUT:

LOAD USERID,

     INTERACTIONID,

     SCORE,

     TIME,

     if(SCORE>=17 or peek(SCORE)>=17, 1,0) as Flag

FROM

[http://community.qlik.com/thread/109994]

(html, codepage is 1252, embedded labels, table is @1);

Then create a table box in your UI with USERID, INTERACTIONID, SCORE and TIME and select Flag = 1 in a list box to filter.

[MarcoWedel · Accepted solution]

Hi Ciaran,

to be more flexible with your limit value you could create one field containing the maximum value of the current and the previous score:

tabData:

LOAD

  USERID,

  INTERACTIONID,

  SCORE,

  if(SCORE>Previous(SCORE), SCORE, Previous(SCORE)) as SCORE_SELECTOR,

  TIME

FROM

[http://community.qlik.com/thread/109994]

(html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

[srchilukoori]

If you are trying to display the scores +/- 2 of what you have selected, this will help.

SC

[ciaran_mcgowan]

Hi Marco,

That worked. Thank you so much for your help. I was wondering if you could help me out again though? I have been asked (on top of the original calculation) to only display the records that occur within 2 hours of each other. So now it would have to display all scores over 17 and the record that follows it, but only if the second record was taken within two hours.

Thanks.