topic Re: Display selected rows and the x number of rows that follow in QlikView

Display selected rows and the x number of rows that follow

ciaran_mcgowan — Sat, 08 Mar 2014 20:33:09 GMT

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.

Re: Display selected rows and the x number of rows that follow

swuehl — Sat, 08 Mar 2014 20:52:45 GMT

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.

Re: Display selected rows and the x number of rows that follow

MarcoWedel — Sun, 09 Mar 2014 01:15:23 GMT

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

Re: Display selected rows and the x number of rows that follow

srchilukoori — Sun, 09 Mar 2014 16:46:02 GMT

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

Re: Display selected rows and the x number of rows that follow

ciaran_mcgowan — Mon, 10 Mar 2014 02:22:00 GMT

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.