Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Contributor

Show an interval of rows

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

Labels (1)
• Set Analysis Expression

1 Solution

Accepted Solutions

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

3 Replies
Partner - Specialist III

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

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

Contributor
Author

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"}>}

Community Browser