topic Re: Calculate To Date in QlikView

Calculate To Date

Thu, 17 Mar 2016 12:43:57 GMT

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

Output Table:

sunny_talwar — Thu, 17 Mar 2016 12:49:10 GMT

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 — Thu, 17 Mar 2016 12:49:34 GMT

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;