Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

above row value - QV script

Hello All

I have a question

my table is

cust_no   transaction_date

100          01/01/2015

100          08/02/2015

100          12/04/2015

200          08/11/2015

200          10/12/2015

I want to do 2 things:

1. new field that is doing something like rownum() in sql

2.new field that giving each row the date above it

all of this by doing only once "order by" on resident

the table that I want should look like:

cust_no   transaction_date  end_date         rank

100          01/01/2015          08/02/2015      1

100          08/02/2015         12/04/2015       2

100          12/04/2015         sysdate           3

200          08/11/2015          10/12/2015     1

200          10/12/2015          sysdate          2

5 Replies
Highlighted

sysdate means today's date???

Highlighted
Contributor
Contributor

yes

Highlighted

Try this may be:

Table:

LOAD * Inline [

cust_no,  transaction_date

100,          01/01/2015

100,          08/02/2015

100,          12/04/2015

200,          08/11/2015

200,          10/12/2015

];

NewTable:

LOAD cust_no,

  transaction_date,

  If(Peek('cust_no') = cust_no, Peek('rank') + 1, 1) as rank

Resident Table;

FinalTable:

LOAD *,

  Date(If(Peek('cust_no') = cust_no, Peek('transaction_date'), today())) as end_date

Resident NewTable

Order By cust_no, rank desc;

DROP Table Table;


Capture.PNG

Highlighted
Contributor
Contributor

IS THERE A WAY NOT DOING TWO ORDER BY

1. FOR RANK

2. FOR DATE

Highlighted

This?

Table:

LOAD *,

  If(Previous(cust_no) = cust_no, Peek('rank') + 1, 1) as rank;

LOAD * Inline [

cust_no,   transaction_date

100,          01/01/2015

100,          08/02/2015

100,          12/04/2015

200,          08/11/2015

200,          10/12/2015

];

FinalTable:

LOAD *,

  Date(If(Peek('cust_no') = cust_no, Peek('transaction_date'), today())) as end_date

Resident Table

Order By cust_no, transaction_date desc;

DROP Table Table;