Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

nutcracker
New Contributor

Peek Value | Carry max value of previously read records

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Peek Value | Carry max value of previously read records

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;

2 Replies
MVP
MVP

Re: Peek Value | Carry max value of previously read records

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
Valued Contributor II

Re: Peek Value | Carry max value of previously read records

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.