topic Re: Evaluate surrounding rows in QlikView

Evaluate surrounding rows

Anonymous — Tue, 22 Aug 2017 16:32:45 GMT

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!

Re: Evaluate surrounding rows

Kushal_Chawda — Tue, 22 Aug 2017 17:19:02 GMT

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;

Re: Evaluate surrounding rows

Anonymous — Tue, 22 Aug 2017 17:49:41 GMT

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.

Re: Evaluate surrounding rows

Kushal_Chawda — Wed, 23 Aug 2017 09:37:13 GMT

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

Re: Evaluate surrounding rows

Anonymous — Wed, 23 Aug 2017 09:43:06 GMT

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.

Re: Evaluate surrounding rows

tresB — Wed, 23 Aug 2017 09:51:13 GMT

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)

)

Re: Evaluate surrounding rows

tresB — Wed, 23 Aug 2017 09:56:02 GMT

A simpler expression without IFs:

Fabs(

RangeSum(

Value=Above(Value),

Value=Above(Value,2),

Value=Below(Value),

Value=Below(Value,2)

)

Re: Evaluate surrounding rows

Anonymous — Wed, 23 Aug 2017 10:05:57 GMT

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

Re: Evaluate surrounding rows

Kushal_Chawda — Wed, 23 Aug 2017 10:09:25 GMT

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

Re: Evaluate surrounding rows

marcus_sommer — Wed, 23 Aug 2017 12:45:30 GMT

Both questions are the same, right?

Custom Range Functions for e.g. Above with count - in expression (no script)

- Marcus

Re: Evaluate surrounding rows

tresB — Wed, 23 Aug 2017 12:58:44 GMT

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.

Re: Evaluate surrounding rows

marcus_sommer — Wed, 23 Aug 2017 13:22:25 GMT

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

Re: Evaluate surrounding rows

sunny_talwar — Wed, 23 Aug 2017 13:30:13 GMT

May be something like this

Range Concat or Something Similar | Qlik Community

Re: Evaluate surrounding rows

MarcoWedel — Thu, 24 Aug 2017 21:33:07 GMT

Hi,

maybe one dynamic solution might be:

using this 'magic' expression:

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

hope this helps

regards

Marco

Re: Evaluate surrounding rows

Anonymous — Fri, 25 Aug 2017 07:46:12 GMT

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!

Re: Evaluate surrounding rows

sunny_talwar — Fri, 25 Aug 2017 09:54:32 GMT

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