Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Evaluate surrounding rows

Hi,

given e.g. a table:

IDValue
1A
2B
3B
4A
5B

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!

1 Solution

Accepted Solutions
Highlighted

Re: Evaluate surrounding rows

Hi,

maybe one dynamic solution might be:

QlikCommunity_Thread_272039_Pic2.JPG

QlikCommunity_Thread_272039_Pic1.JPG

QlikCommunity_Thread_272039_Pic3.JPG

using this 'magic' expression:

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

hope this helps

regards

Marco

View solution in original post

15 Replies
Highlighted
MVP
MVP

Re: Evaluate surrounding rows

Data:

LOAD ID,

          Value

FROM table;

New:

LOAD ID,

          Value,

          rowno() as Row

Resident Data

order by ID;

drop table Data;

left join(New)

LOAD Value,

          1 as Flag

Resident Data

where Row=1;

Final:

LOAD *,

         if(Flag=1,0,rangesum(peek(Magic),1)) as Magic

resident New;

drop table New;

Highlighted
Contributor II
Contributor II

Re: Evaluate surrounding rows

I'm sorry - I wasn't clear enough.

I want this without the script. Only expressions in a straight table.

I want to be able to e.g. set the number of rows as a variable, changeable by the user.

Highlighted
MVP
MVP

Re: Evaluate surrounding rows

I am not sure about this set the number of rows as a variable, changeable by the user.

what do you mean by this? Can you please give one example with what exactly you want to achieve

Highlighted
Contributor II
Contributor II

Re: Evaluate surrounding rows

In my original post I wrote "surround the current row up to 2 rows in each direction"

Now I want that this number - 2 - is stored in a variable and can be change by the user via an input field.

This is not a critical reqirement anyway, because I can just use $(vLookahead) instead of 2.

More important is the requirement that is is not in the load-script, but in an expression.

I want to be able to highlight a cell, if the current cell is surrounded by more than e.g. 3 rows with the same value.

An this should work also, when the user changes the order of the table.

Highlighted
MVP
MVP

Re: Evaluate surrounding rows

May be like this?

RangeCount(

            If(Value=Above(Value),1),

            If(Value=Above(Value,2),1),

            if(Value=Below(Value),1),

            if(Value=Below(Value,2),1)

  )

Capture.PNG

Highlighted
MVP
MVP

Re: Evaluate surrounding rows

A simpler expression without IFs:

Fabs(

RangeSum(

  Value=Above(Value),

  Value=Above(Value,2),

  Value=Below(Value),

  Value=Below(Value,2)

  )

)

Highlighted
Contributor II
Contributor II

Re: Evaluate surrounding rows

That's pretty much on the path where I am now.

however, now there comes the requirement that I want the 2 be variable (see my answer to kush141087‌)

Highlighted
MVP
MVP

Re: Evaluate surrounding rows

I don't think this will be achievable from front end because input field function only works from script.

Highlighted
MVP & Luminary
MVP & Luminary

Re: Evaluate surrounding rows