Use "peek" or "above"?

[#1]

Hello,

I have a table that is basically

RECNO  ID       CODE     TYPE

1              1        3344       before

2              1        3344       after

3              2       3598       before

4              2       3622       after

5             3        3722       before

6             3       2144       after

    --  etc. ---

What I want to do is select all the CODE that is 1) "after", and 2) where the 'after' CODE is different from the 'before' for that ID,

e.g.

In the table above, I want to select codes 4 and 6, since they are both "after", and since their CODE does not match the

CODE of the 'before' with the same ID. (Note: the table is ordered by RECNO and then ID, so the 'before' code is always

on top of the 'after'.)

So, in my LOAD statement, do I put something like:

IF(above(CODE)<> CODE AND TYPE='after',CODE,Null())

(table is huge and takes 15 min to load, so rather find out if this is right before I throw it at the machine)

Thanks,

Kevin

[#2]

oops..

IF(Above(CODE)<>CODE And TYPE='after',CODE,Null()) AS AfterCode,

[swuehl · Accepted solution]

Above() is a chart inter record function and thus can only be used in chart expressions, not in the load script.

You can use peek() or previous() to access a record in your load already loaded.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/08/peek-vs-previous-when-to-use-each

[its_anandrjs]

You can use previous and yes above is the chart function you can use it only any chart.

IF(Previous(CODE)<>CODE And TYPE='after',CODE,Null()) AS AfterCode,

[MK_QSL]

Temp:

Load * Inline

[

RECNO,  ID,       CODE, TYPE

1,              1,        3344, before

2,              1,        3344, after

3,              2,       3598, before

4,              2,       3622, after

5,             3,        3722, before

6,             3,       2144, after

];

NoConcatenate

Final:

Load

  RECNO,

  ID,

  CODE,

  TYPE,

  IF(CODE <> Previous(CODE) and  TYPE = 'after' , CODE, Null()) as RESULT

  Resident Temp

Order By RECNO;

Drop Table Temp;

[its_anandrjs]

You can load table like

Tmp:

LOAD * INLINE [

    RECNO,  ID, CODE,     TYPE

    1,      1,        3344,       before

    2,      1,        3344,       after

    3,      2,        3598 ,      before

    4,      2,        3622  ,     after

    5,      3,        3722 ,      before

    6,      3,        2144 ,      after

];

NoConcatenate

FinalTable:

LOAD    RECNO,  ID,  CODE,   TYPE,

IF( Previous(CODE) <> CODE And TYPE = 'after', CODE, Null( ) ) AS AfterCode

Resident Tmp Order by RECNO, ID;

DROP Table Tmp;

[maxgro]

source:

load * inline [

RECNO , ID ,      CODE ,    TYPE

1     ,         1  ,      3344    ,   before

2     ,         1  ,      3344    ,   after

3     ,         2  ,     3598     ,  before

4     ,         2  ,     3622     ,  after

5     ,        3   ,     3722     ,  before

6      ,       3   ,    2144      , after

];

table:

NoConcatenate load *

Resident source

where TYPE = 'after' and previous(CODE) <> CODE

order by CODE, TYPE desc;

drop table source;