Peek Value | Carry max value of previously read records

[Anonymous]

Hi All,

I have the following table

ID,	Owner,	Start,	End,	ExpectedResult,
1,	A,	2,	8,	8,
2,	A,	4,	6,	8,
3,	A,	5,	7,	8,
4,	A,	9,	12,	12,
5,	B,	3,	4,	4,
6,	B,	5,	13,	13,
7,	B,	8,	10,	13,
8,	B,	11,	12,	13,
9,	B,	15,	17,	17,
10,	B,	16,	18,	18

I cannot correctly peek the expected result. The following conditions are in play

1. Table is grouped by Owner and sorted ASC Start

2. If Owner = Previous(Owner) then check if the current start row is GREATER than ANY PREVIOUSLY read end row for that OWNER

if it is, then use that rows end date otherwise use the previously read row.

My ExpectedResult shows you what I am after. How would I write this in script

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD * INLINE [

    ID, Owner, Start, End

    1, A, 2, 8

    2, A, 4, 6

    3, A, 5, 7

    4, A, 9, 12

    5, B, 3, 4

    6, B, 5, 13

    7, B, 8, 10

    8, B, 11, 12

    9, B, 15, 17

    10, B, 16, 18

];

FinalTable:

LOAD *,

If(Owner = Previous(Owner), RangeMax(Peek('ExpectedResult'), End), End) as ExpectedResult

Resident Table

Order By Owner, ID;

DROP Table Table;

[pablolabbe]

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.