Using a field to populate a new field

[#1]

Hey all,

I am trying to create a finish date based on the start date of the item after it. I need this to be in the script so that I can use the finish date for multiple items. Here is a sample of the data:

TargetSymbol	StageDesignation	StageOrde	StageStartDate	StageFinishDate
AAA	A	1	3/4/2008
AAA	C	4	6/20/2011
AAA	D0	5	4/12/2012
AAA	D1	6	6/26/2012
AAA	D2	7	10/16/2013
AAA	E1	8	1/15/2012
AAA	E2	9	2/15/2014
AAA	E3	10	3/15/2014
AAA	E4	11	9/15/2014
AAA	F1	12	2/15/2015
AAA	E4	11	4/15/2015
AAA	F3	14	5/15/2015
AAA	G1	15	6/25/2015
XYZ	D1	6	3/4/2008
XYZ	E1	8	6/13/2008
XYZ	F1	12	9/1/2008
XYZ	G1	15	2/25/2009
XYZ	H	17	5/28/2009
XYZ	I	18	10/13/2009
XYZ	J	19	1/1/2012
XYZ	K	20	5/15/2015

The finish date of the Stage is start date of next stage -1day.

the stages can go back and forward again, see row 11, so order needs to be determined by the date that occurs in front of it.

Let me know if I need further clarification

Thanks for all your help.

Vince

[MarcoWedel]

Hi,

one solution could be:

table1:
LOAD TargetSymbol,
    StageDesignation,
    StageOrde,
    StageStartDate
FROM [https://community.qlik.com/thread/181709] (html, codepage is 1252, embedded labels, table is @1)
Where Len(TargetSymbol);

Left Join (table1)
LOAD TargetSymbol,
    StageStartDate,
    If(TargetSymbol=Previous(TargetSymbol),Date(Previous(StageStartDate)-1)) as StageFinishDate
Resident table1
Order By TargetSymbol, StageStartDate desc;

hope this helps

regards

Marco

[#3]

Hey Oleg,

Soooooo close - now I am getting the correct date, but also the startdate of the same stage as well.

Load

*,

    if(TargetSymbol=Peek(TargetSymbol), date(Previous(StageStartDate)-1, 'M/D/YYYY'), null()) as StageFinishDate

Resident

TargetDBLoad

order by TargetSymbol, StageStartDate desc;



drop table TargetDBLoad;

[#4]

When I put the following in, to make sure it was not comparing same stage to same stage - it seems to fix it.

Load

*,

    if(TargetSymbol=Peek(TargetSymbol) and StageDesignation<>peek(StageDesignation), date(Previous(StageStartDate)-1, 'MM/DD/YYYY')) as StageFinishDate

Resident

TargetDBLoad

order by TargetSymbol, StageStartDate desc;



drop table TargetDBLoad;