topic Show an interval of rows in QlikView

Show an interval of rows

borisjaksicib — Sat, 16 Nov 2024 00:26:15 GMT

Data model has 2 fields, ID (sequential interger numbers) , and Log (text)

I have the following problem:

on user selecting a value in field Log (or multiple values!) show all rows that have these values, as well as the preceding and following 5 lines (if possible make it so that the user can change 5 to any number he desires)

with some basic set analysis I can get this to work, but only if the selected Log value occurs only once

sum({<Log=, ID = {"<=$(=max(ID+5))>=$(=max(ID-5))"}>} 1)

as soon as the user selection covers more that one row this of course only works for the row with the maximum ID

Re: Show an interval of rows

Arthur_Fong — Wed, 24 Jun 2020 02:19:54 GMT

You have a sample app and the expected output for this?

Re: Show an interval of rows

marcus_sommer — Thu, 25 Jun 2020 14:24:17 GMT

I think there are various approaches possible. One could be to chain these ID's with The-As-Of-Table. Another might to use an aggr() construct for it. A further possibility would be to use concat() to fetch and combine all wanted ID's - this might be look like:

... ID = {$(='(' & concat(distinct (ID - 1) & '|' & ID & '|' & (ID + 1), '|') & ')')} ...

here just reduced for the readability to +- 1

- Marcus

Re: Show an interval of rows

borisjaksicib — Thu, 25 Jun 2020 14:48:18 GMT

Thanks, managed to solve like this:

vPrecedingLines input variable

vFollowingLines input variable

vSelect is calculated as following:

='{<Log=, ID={'&chr(34)&'>='&concat(distinct (ID-$(vPrecedingLines))&'<='&(ID+$(vFollowingLines)), chr(34)&','&chr(34)&'>=') &chr(34)&'}>}'

and replaces set analysis in the expression

example of output:

{<Log=, ID={">=36637<=36672",">=557904<=557939",">=70580<=70615"}>}