Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Use "peek" or "above"?

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

6 Replies
Not applicable
Author

oops..

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

swuehl
MVP
MVP

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

View solution in original post

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
MVP
MVP

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
MVP
MVP

1.png

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;