Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
borisjaksicib
Contributor
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

Capture.PNG

 

Labels (1)
1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

3 Replies
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

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

marcus_sommer

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

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