Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Thanks Andrew
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;
Thanks Anand its very helpful...