above row value - QV script

[dvirus321]

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

[sunny_talwar]

sysdate means today's date???

[dvirus321]

yes

[sunny_talwar]

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;

[dvirus321]

IS THERE A WAY NOT DOING TWO ORDER BY

1. FOR RANK

2. FOR DATE

[sunny_talwar]

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;