How to create 'From' and 'Till' dates from a single date field ?

[harendra_singh]

Hello All,

I want to know how to create a snapshot i.e., From and till from a single date field. For example, An inventory has a product 'X' cost xyz upto this date and changes after this date.

Items	Price	Date
Apple	10	05/01/2017
Banana	20	10/01/2017
Apple	15	20/01/2017
Guava	30	17/01//2017
Guava	40	25/01/2017

I need this as a result:

Itemss	Price	From	Till
Apple	10	05/01/2017	19/01/2017
Apple	15	20/01/2017
Banana	20	10/01/2017
Guava	30	17/01/2017	24/01/2017
Guava	40	25/01/2017

Any help would be helpful. Thanks anyway

[effinty2112 · Accepted solution]

Hi Harendra,

Try:

Data:

LOAD * INLINE [

    Products, Price, Date

    Apple, 10, 05/01/2017

    Banana, 20, 10/01/2017

    Apple, 15, 20/01/2017

    Guava, 30, 17/01/2017

    Guava, 40, 25/01/2017

];

OrderedData:

LOAD

Products,

Price,

Date as From,

if(Products = Peek(Products) , Date(Peek(From)-1), Date(Today())) as To

Resident Data Order by Products, Date desc;

DROP Table Data;

Giving:

Products	From	To	Price
Apple	05/01/2017	19/01/2017	10
Apple	20/01/2017	30/08/2017	15
Banana	10/01/2017	30/08/2017	20
Guava	17/01/2017	24/01/2017	30
Guava	25/01/2017	30/08/2017	40

If you prefer you can get

Products	From	To	Price
Apple	05/01/2017	19/01/2017	10
Apple	20/01/2017		15
Banana	10/01/2017		20
Guava	17/01/2017	24/01/2017	30
Guava	25/01/2017		40

by changing the line of script that defines the field To:

if(Products = Peek(Products) , Date(Peek(From)-1)) as To

cheers

Andrew

[harendra_singh]

Thanks Andrew

[its_anandrjs]

Create your table this ways and use PEEK and PREVIOUS, Order By

Source:
LOAD Products,  Price, Date(Date#(Date,'DD/MM/YYYY')) as Date;
LOAD * Inline
[
Products,  Price,     Date
Apple,     10,  05/01/2017
Banana,    20,  10/01/2017
Apple,     15,  20/01/2017
Guava,     30,  17/01/2017
Guava,     40, 25/01/2017
];

NoConcatenate
Temp1:
LOAD
Products,
Price,
Date as From_Date,
if(Products = Peek(Products),Date(Previous(Date)-1),'') as To_Date
Resident Source
Order By Products,Date desc;
DROP Table Source;

NoConcatenate
Final:
LOAD * Resident Temp1 Order By Price;
DROP Table Temp1;

EXIT Script;

[harendra_singh]

Thanks Anand its very helpful...