
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
oops..
IF(Above(CODE)<>CODE And TYPE='after',CODE,Null()) AS AfterCode,


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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,

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
