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

[maxgro]

is every row a stage?

[Oleg_Troyansky]

Hi Vince,

you should load your tables sorted by the necessary key fields (such as a Target Symbol?) and a Start Date, in descending order, and then use functions peek() and previous() to calculate the Finish Date. When the rows are sorted in the descending order, the previous row in fact represents the next stage.

Search this forum for a recent discussion on Peek() and Previous(), and check out the following document:

Peek() or Previous() ?

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

[#4]

Every row that is of a concern - i.e. some do not, but I do not need that data

[pamaxeed]

Hi Vince,

attached a sample application.

I am not sure about the sort order, i added an additional field which represented the original load order as numeric field.

Try to give a look and if it could work.

Regards,

Patric

[#6]

I am trying to do what you suggest, like this:

Load

TargetSymbol,

    if(TargetSymbol= Previous(TargetSymbol),Peek(StageStartDate,+1)) as StageFinishDate

Resident

TargetDBLoad

group by TargetSymbol

order by StageStartDate desc;

But I keep getting this error:

[pamaxeed]

No Aggregation Function is used in that Load statement, so it fails.

Try to give a look to my sample and let me know ...

[pamaxeed]

Here a sample app where I am sorting by TargetSymbol and StageStartDate taking in account the different TargetSymbols.

Cheers,

Patric

[#9]

Hey Patric,

I am typing it this way:

Load

TargetSymbol,

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

Resident

TargetDBLoad

order by TargetSymbol, StageStartDate desc;

and seeing every finish assigned to every start

[Oleg_Troyansky · Accepted solution]

Vince,

I think you are creating another table that is linked "many-to-many" to the original table. In order to avoid that, you should have something like this (notice the asterisk that signifies "all existing fields")  :

TargetDBLoad_New:

Load

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

Resident

TargetDBLoad

order by TargetSymbol, StageStartDate desc;

drop table TargerDBLoad;

cheers,

Oleg Troyansky