Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using a field to populate a new field

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:

TargetSymbolStageDesignationStageOrdeStageStartDateStageFinishDate
AAAA13/4/2008
AAAC46/20/2011
AAAD054/12/2012
AAAD166/26/2012
AAAD2710/16/2013
AAAE181/15/2012
AAAE292/15/2014
AAAE3103/15/2014
AAAE4119/15/2014
AAAF1122/15/2015
AAAE4114/15/2015
AAAF3145/15/2015
AAAG1156/25/2015
XYZD163/4/2008
XYZE186/13/2008
XYZF1129/1/2008
XYZG1152/25/2009
XYZH175/28/2009
XYZI1810/13/2009
XYZJ191/1/2012
XYZK205/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

1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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


View solution in original post

12 Replies
maxgro
MVP
MVP

is every row a stage?

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

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

pamaxeed
Partner - Creator III
Partner - Creator III

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

Not applicable
Author

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:

error.jpg

pamaxeed
Partner - Creator III
Partner - Creator III

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
Partner - Creator III
Partner - Creator III

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

Cheers,

Patric

Not applicable
Author

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

error2.jpg

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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