Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
cancel
Showing results for 
Search instead for 
Did you mean: 
lanlizgu
Creator III
Creator III

Peek with group of two rows

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

    

Field1DateValueExpectedValue
101/01/201733
101/01/201852
201/01/201766
201/01/201893

The query I am using

LOAD

Field1

Date

Value-Peek(Value)) as ExpectedValue,

resident MyTable

order by Field1  asc

;

1 Solution

Accepted Solutions
jaumecf23
Creator III
Creator III

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

];

View solution in original post

4 Replies
m_woolf
Master II
Master II

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

Chanty4u
MVP
MVP

may be

  1. left join(A) 
  2. LOAD 
  3.     Date, 
  4.     Filed1, 
  5.     if([Field1] = Previous([Field1]), [Value] - Previous([Value])) as Exp 
  6. Resident 
  7.     A 
  8. Order by Date, Field1; 
jaumecf23
Creator III
Creator III

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

];

lanlizgu
Creator III
Creator III
Author

Thank you Jaume, working correctly.