QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
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

 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)
Tags (2)
1 Solution

Accepted Solutions
MVP

Re: Calculate To Date

Try this:

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

];

FinalTable:

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;

2 Replies
MVP

Re: Calculate To Date

Try this:

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

];

FinalTable:

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;

Re: Calculate To Date

Perhaps like this:

Temp:

Result:

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