Timeline problem - calculation during skript loading

[#1]

Dear Community,

I have a problem with visualization of certain data in QlikView. It is not easy to describe where the problem realy is, but I try my very best.
In the external database the following data were given:

Order Cutoff_date Cutoff_time Duration
A 20101126 170000 120
B 20101126 170000 180
C 20101126 170000 96...

During script loading the following data should been calculated:
End_time:
Here the end of one order should been calculated - take last Start_time (except first record of each day here the cutoff_time should been taken).

Start_time:
Here the start time of one order should been calculated - end_time minus duration.

And here is the problem both "new" fields depend each other - an error-message appears.

The result after the loading process should be:

Order Cutoff_date Cutoff_time Duration Beginn End
A 20101126 170000 120 165800 170000
B 20101126 170000 180 165500 165800
C 20101126 170000 960 163900 165500
D 20101126 170000 150 163630 163900
E 20101126 150000 120 145800 150000
F 20101126 150000 180 145500 145800
G 20101129 170000 480 165200 170000
H 20101129 170000 240 164800 165200
I 20101129 170000 60 164700 164800

But if I add the following code to the script QilkView get the respond that the field does not exist.
TABLE_I:

// generate start time

Load *

, if(RowNo()=1, H_CUTOFF_TIME - (H_DURATION / 86400), Previous(H_START_TIME) - (H_DURATION / 86400)) as H_START_TIME

;

// load only header

Load PRLFDN //to connect to the origin database

, ORDER as H_ORDER

, PRRETI as H_DURATION

, date#(PRDTB, 'YYYYMMDD') as H_CUTOFF_DATE

, time#(num(PRTIB, '000000'), 'hhmmss') as H_CUTOFF_TIME

Resident TABLE where FIELD1='WP' and FIELD2 = ' ' order by ORDERDATE desc, ORDERTIME desc;

Is there anyone who had a similar problem?

I tried to use variables but it doesn't work to set a variable dynamic during the loadprocess.

Next challenge is to make a visualization to that data!

Kind regards.

Martin Brunkalla

[#2 · Accepted solution]

Hello All,

Its solved within the follwing code:

TABLE_I:
// generate start time
//Load *
// , if(RowNo()=1, H_CUTOFF_TIME - (H_DURATION / 86400), Previous(H_STARTTIME) - (H_DURATION / 86400)) as H_STARTTIME
//;
Load*
, H_END - (H_DURATION / 86400) as H_BEGIN
;
Load *
, if(H_CUTOFF_TIME > peek(H_BEGIN) and H_CUTOFF_DATE=Previous(H_CUTOFF_DATE), previous(H_CUTOFF_TIME) - ((H_SUMTIME - H_DURATION)/86400), H_CUTOFF_TIME) as H_END
;
Load *
// same Cutoff-date
, if(H_CUTOFF_DATE=Previous(H_CUTOFF_DATE),
// and same Cutoff-time
if (H_CUTOFF_TIME=Previous(H_CUTOFF_TIME),
//sum the duration
numsum(H_DURATION, peek('H_SUMTIME')),
//CUTOFF time not equal
if (H_CUTOFF_TIME > peek(H_BEGIN),
numsum(H_DURATION, peek('H_SUMTIME')),
H_DURATION)),
// if the date changes remeber only the duration
H_DURATION)
as H_SUMTIME
;
//Load *
// , if(H_CUTOFF_DATE=Previous(H_CUTOFF_DATE) and H_CUTOFF_TIME=Previous(H_CUTOFF_TIME),
// numsum(H_DURATION, peek('H_SUMTIME')),H_DURATION) as H_SUMTIME
//;
// load only workpackages
// load only header
Load PRLFDN //to connect to the origin database
, PRRNR as H_PRPRN
, PRRETI as H_DURATION
, date#(PRDTB, 'YYYYMMDD') as H_CUTOFF_DATE
, time#(num(PRTIB, '000000'), 'hhmmss') as H_CUTOFF_TIME
Resident PRMPROC where PRRCTYP='WP' and PRWSTP = ' ' order by PRDTB desc, PRTIB desc;