topic Re: above row value - QV script in QlikView

above row value - QV script

dvirus321 — Wed, 11 Nov 2015 14:46:58 GMT

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

Re: above row value - QV script

sunny_talwar — Wed, 11 Nov 2015 15:04:27 GMT

sysdate means today's date???

Re: above row value - QV script

dvirus321 — Wed, 11 Nov 2015 15:06:32 GMT

yes

Re: above row value - QV script

sunny_talwar — Wed, 11 Nov 2015 15:10:31 GMT

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;

Re: above row value - QV script

dvirus321 — Wed, 11 Nov 2015 15:12:47 GMT

IS THERE A WAY NOT DOING TWO ORDER BY

1. FOR RANK

2. FOR DATE

Re: above row value - QV script

sunny_talwar — Wed, 11 Nov 2015 16:36:43 GMT

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;