Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
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.