Calculate To Date

[#1]

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  

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

Output Table:

Product	Date	To Date
A	01/01/2013	22/04/2014
A	23/04/2014	07/07/2014
A	08/07/2014	17/03/2016 (Today Date)
B	13/01/2016	08/02/2016
B	09/02/2016	29/03/2016
B	01/03/2016	17/03/2016 (Today Date)

[sunny_talwar · Accepted solution]

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;

[Gysbert_Wassenaar]

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;