Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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) |
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;
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;
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;