topic Re: Peek with group of two rows in QlikView

Peek with group of two rows

lanlizgu — Thu, 14 Jun 2018 11:54:27 GMT

I am trying to use the peek function to sustract the previous value without success.

What I would like to do is obtain the value from the expected value column. However, for the third line I am getting a 1 (6-5) instead of getting the first value of the second Field 1=2, as i understand that I am missing an aggregation by Field1 or something like that

Field1	Date	Value	ExpectedValue
1	01/01/2017	3	3
1	01/01/2018	5	2
2	01/01/2017	6	6
2	01/01/2018	9	3

The query I am using

LOAD

Field1

Date

Value-Peek(Value)) as ExpectedValue,

resident MyTable

order by Field1 asc

;

Re: Peek with group of two rows

m_woolf — Thu, 14 Jun 2018 12:49:23 GMT

I didn't test this:

LOAD

Field1

Date

if(Field1 = peek('Field1'),Value-Peek('Value'),Value) as ExpectedValue,

resident MyTable

order by Field1 asc,Date asc

Re: Peek with group of two rows

Chanty4u — Thu, 14 Jun 2018 12:51:41 GMT

may be

left join(A)
LOAD
Date,
Filed1,
if([Field1] = Previous([Field1]), [Value] - Previous([Value])) as Exp
Resident
A
Order by Date, Field1;

Re: Peek with group of two rows

jaumecf23 — Thu, 14 Jun 2018 12:54:02 GMT

Hi,

Try this:

Table1:

Load*,

if(peek(Field1)<>Field1,Value,Value-peek(Value)) as ExpectedValue

;

LOAD * inline [

Field1, Date, Value

1, 01/01/2017, 3

1, 01/01/2018, 5

2, 01/01/2017, 6

2, 01/01/2018, 9

];

Re: Peek with group of two rows

lanlizgu — Thu, 14 Jun 2018 14:54:37 GMT

Thank you Jaume, working correctly.