topic Re: add field in a table on script in QlikView

add field in a table on script

tyagishaila — Thu, 18 Feb 2016 08:11:38 GMT

Need Solution on script not on dashboard

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 — Thu, 18 Feb 2016 08:22:24 GMT

- Marcus

MayilVahanan — Thu, 18 Feb 2016 08:32:40 GMT

Try like this

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 — Thu, 18 Feb 2016 08:44:33 GMT

Superb, its working.

Thanks MAYIL

tyagishaila — Thu, 18 Feb 2016 08:45:15 GMT

Thanks Marcus,

Your reply is really helpful for me.

cheers