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