Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
avowinkel
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
MarcoWedel

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
Kushal_Chawda

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;

avowinkel
Contributor II
Contributor II
Author

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.

Kushal_Chawda

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

avowinkel
Contributor II
Contributor II
Author

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.

tresesco
MVP
MVP

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

tresesco
MVP
MVP

A simpler expression without IFs:

Fabs(

RangeSum(

  Value=Above(Value),

  Value=Above(Value,2),

  Value=Below(Value),

  Value=Below(Value,2)

  )

)

avowinkel
Contributor II
Contributor II
Author

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‌)

Kushal_Chawda

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

marcus_sommer
MVP & Luminary
MVP & Luminary