Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Highlighted
Not applicable

Calculate To Date

Hi,

I Have a Table contains 2 columns,
I want to add to this table 'To Date' column contains next  row 'From Date' minus a day.

Can someone have an efficient way to do that?

Many thanks

Jacob

Original Table  

ProductFrom Date
A01/01/2013
A23/04/2014
A08/07/2014
B13/01/2016
B09/02/2016
B01/03/2016

Output Table:

   

ProductDateTo Date
A01/01/201322/04/2014
A23/04/201407/07/2014
A08/07/201417/03/2016 (Today Date)
B13/01/201608/02/2016
B09/02/201629/03/2016
B01/03/201617/03/2016 (Today Date)
Tags (2)
1 Solution

Accepted Solutions

Re: Calculate To Date

Try this:

Table:

LOAD * Inline [

Product, From Date

A, 01/01/2013

A, 23/04/2014

A, 08/07/2014

B, 13/01/2016

B, 09/02/2016

B, 01/03/2016

];

FinalTable:

LOAD *,

  If(Product = Peek('Product'), Date(Peek('From Date') - 1), Date(Today())) as [To Date]

Resident Table

Order By Product, [From Date] desc;

DROP Table Table;


Capture.PNG

2 Replies

Re: Calculate To Date

Try this:

Table:

LOAD * Inline [

Product, From Date

A, 01/01/2013

A, 23/04/2014

A, 08/07/2014

B, 13/01/2016

B, 09/02/2016

B, 01/03/2016

];

FinalTable:

LOAD *,

  If(Product = Peek('Product'), Date(Peek('From Date') - 1), Date(Today())) as [To Date]

Resident Table

Order By Product, [From Date] desc;

DROP Table Table;


Capture.PNG

Re: Calculate To Date

Perhaps like this:

Temp:

LOAD Product, Date FROM ...source;

Result:

LOAD

     Product,

     Date,

     If( Product =Previous(Product),Previous(Date), Date(Today(),'DD/MM/YYYY')) as [To Date]

RESIDENT

     Temp

Order By Product, Date desc;


talk is cheap, supply exceeds demand
Community Browser