Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
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
MVP
MVP

Re: Using a field to populate a new field

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


12 Replies
MVP
MVP

Re: Using a field to populate a new field

is every row a stage?

MVP
MVP

Re: Using a field to populate a new field

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

Re: Using a field to populate a new field

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

pamaxeed
Contributor III

Re: Using a field to populate a new field

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

Re: Using a field to populate a new field

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
Contributor III

Re: Using a field to populate a new field

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
Contributor III

Re: Using a field to populate a new field

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

Cheers,

Patric

Not applicable

Re: Using a field to populate a new field

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

MVP
MVP

Re: Using a field to populate a new field

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


Community Browser