topic Re: Using a field to populate a new field in QlikView

Using a field to populate a new field

Mon, 21 Sep 2015 19:23:51 GMT

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

Re: Using a field to populate a new field

maxgro — Mon, 21 Sep 2015 19:43:15 GMT

is every row a stage?

Re: Using a field to populate a new field

Oleg_Troyansky — Mon, 21 Sep 2015 19:45:26 GMT

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

Re: Using a field to populate a new field

Mon, 21 Sep 2015 19:46:03 GMT

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

Re: Using a field to populate a new field

pamaxeed — Mon, 21 Sep 2015 20:06:55 GMT

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

Re: Using a field to populate a new field

Mon, 21 Sep 2015 20:09:04 GMT

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:

Re: Using a field to populate a new field

pamaxeed — Mon, 21 Sep 2015 20:20:20 GMT

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

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

Re: Using a field to populate a new field

pamaxeed — Mon, 21 Sep 2015 20:26:39 GMT

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

Cheers,

Patric

Re: Using a field to populate a new field

Mon, 21 Sep 2015 20:56:51 GMT

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

Re: Using a field to populate a new field

Oleg_Troyansky — Mon, 21 Sep 2015 21:10:02 GMT

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

Re: Using a field to populate a new field

MarcoWedel — Mon, 21 Sep 2015 23:14:53 GMT

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

Re: Using a field to populate a new field

Mon, 21 Sep 2015 23:47:05 GMT

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;

Re: Using a field to populate a new field

Mon, 21 Sep 2015 23:59:40 GMT

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;