Evaluate surrounding rows

[Anonymous]

Hi,

given e.g. a table:

ID	Value
1	A
2	B
3	B
4	A
5	B

I want to be able to evaluate the surrounding rows of every row.

That means for example to get an expression that results in this additional column:

magic
0
1
2
0
1

It tells, how many of the identical values surround the current row up to 2 rows in each direction.

1: is A -> no A in 2 or 3 => 0

2: is B -> no B in 1, B in 3, no B in 4 => 1

3: is B -> no B in 1, B in 2, no B in 4, B in 5 => 2

I want to be able to do this and similar questions.

Another question would be: How many rows with the same value are (sequentially) above me/below me?

resulting in (for above): 0, 0, 1, 0, 0 - because just the B in 3 has another B above.

Thanks for your help!

[tresesco]

Looks similar, however this one being string comparison, I wish there was a function RangeConcat() , may be the case could have been handled in an efficient way.

[marcus_sommer]

Yes, by a string-comparison it will be more difficult but maybe it could be handled with precedes or follows as operators. If not it will be probably far more complicated because a rangeconcat() doesn't exists and AFAIK couldn't also be directly replaced with something similar because it would need a real loop within an expression.

An alternatively could be a quite ugly expression which concats all values with their needed order (might require additional steps) and some algorithm which picked the right values from there - maybe with some pick(match()) but probably within a lot of nested if-loops. By using the desktop client or the IE plugin could be within the easter eggs macro-functions enabled which could be execute appropriate loops. Both alternatives will need a lot of efforts and are rather the worst case ...

- Marcus

[sunny_talwar]

May be something like this

Range Concat or Something Similar | Qlik Community

[MarcoWedel · Accepted solution]

Hi,

maybe one dynamic solution might be:

using this 'magic' expression:

$(='-('&Concat('Value=Above(Value,'&ValueLoop(-vLookahead,vLookahead)&')',')-(')&')')-1

hope this helps

regards

Marco

[Anonymous]

Thank you all.

There are many more very useful answers here.

When you are thinking about similar problems, make sure that you check out all the answers below!

[sunny_talwar]

You can always mark helpful responses in order for helping future visitors find other helpful responses

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny