Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

dvirus321
New 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

Tags (2)
5 Replies

Re: above row value - QV script

sysdate means today's date???

dvirus321
New Contributor

Re: above row value - QV script

yes

Re: above row value - QV script

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

dvirus321
New Contributor

Re: above row value - QV script

IS THERE A WAY NOT DOING TWO ORDER BY

1. FOR RANK

2. FOR DATE

Re: above row value - QV script

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;