add field in a table on script

[tyagishaila]

Need Solution on script not on dashboard

Item	StartingDate	Price
A	20-05-2015	3999
B	15-02-2013	5000
B	03-01-2014	4500
B	23-01-2014	4999
C	07-01-2012	399
C	08-02-2015	199
D	02-02-2013	4999
E	12-12-2015	2499
E	31-12-2015	1499

I have an above Item table, In which an item has different price on different date.

I need to show a range of dates Starting Date and Ending Date, to know between which dates an item has  what price?

for example

Item B sold on 5000/- since 15-02-2013 to 02-01-2014 (previous date of next price change date of that Item.

Basically I need an EndingDate field

Item	Starting Date	EndingDate	Price
A	20-05-2015	Today Date	3999
B	15-02-2013	02-01-2014	5000
B	03-01-2014	22-01-2014	4500
B	23-01-2014	18-02-2016(Today Date)	4999
C	07-01-2012	07-02-2015	399
C	08-02-2015	Today Date	199
D	02-02-2013	TodayDate	4999
E	12-12-2015	30-12-2015	2499
E	31-12-2015	Today Date	1499

Thanks

[marcus_sommer]

You could do it with Peek() or Previous() ?

- Marcus

[MayilVahanan · Accepted solution]

Hi

Try like this

T:

LOAD Item, date(Date#(StartingDate, 'DD-MM-YYYY')) As StartingDate, Price INLINE [

    Item, StartingDate, Price

    A, 20-05-2015, 3999

    B, 15-02-2013, 5000

    B, 03-01-2014, 4500

    B, 23-01-2014, 4999

    C, 07-01-2012, 399

    C, 08-02-2015, 199

    D, 02-02-2013, 4999

    E, 12-12-2015, 2499

    E, 31-12-2015, 1499

];

Load *,  Date(If(Previous(Item) = Item , Previous(StartingDate) -1, Today())) As EndingDate Resident T Order by Item, StartingDate desc;

DROP Table T;

[tyagishaila]

Superb, its working.

Thanks MAYIL

[tyagishaila]

Thanks Marcus,

Your reply is really helpful for me.

cheers