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.

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

MVP & Luminary
MVP & Luminary

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