How can i get the value of before field in the dynamic table?

[#1]

I working with dynamic table, and have two columns, 1st is start time, 2nd is finish time, and i need a new column with the diference between start time and before finish time, how can i get the value of finish time of before row, i tried to use above(field_name) but i didnt have sucessful.....

Tks!!!l

[jbeierschmitt]

If you sort the table you should be able to utilize the PREVIOUS function to select the row immediately above the record; the key is to sort that field in descending order...

[ code ]

DependentDemand:
%BOMReference_Key & [STPO.Item_POSNR] AS %BOMReferencePOSNR_Key,
if(date#([STPO.Valid From_DATUV],'YYYY-MM-DD') = MAXDATE,$(vToday),previous([STPO.Valid From_DATUV])-1) AS [STPO.Valid To_PREVIOUS],
*
RESIDENT
EXPO_BOM_2_MAT
ORDER BY
%BOMReference_Key ASC,
[STPO.Item_POSNR] ASC,
[STPO.Valid From_DATUV] DESC;

le EXPO_BOM_2_MAT;













le EXPO_BOM_2_MAT; le EXPO_BOM_2_MAT;

[ /code ]

[#3 · Accepted solution]

Hi ealeixo,

Have you tried the previous function? This function is used in the script and the only thing to make sure is that your table is sorted correct when applying previous. Calculating this difference in the script makes you more free when it comes to sorting columns in tables.

Shift_tmp:
LOAD Shift,
time(StartTime,'hh:mm:ss') as StartTime,
time(FinishTime,'hh:mm:ss') as FinishTime
FROM StartTime_FinishTime.xls (biff, embedded labels, table is Blad1$)
;

Shift:
LOAD *,
previous(FinishTime) as PrevFinishTime,
if((StartTime-previous(FinishTime))>0,time((StartTime-previous(FinishTime)),'hh:mm:ss'),time(0,'hh:mm:ss')) as DiffStartPrevFinish
RESIDENT Shift_tmp
ORDER BY StartTime;

DROP TABLE Shift_tmp;

This is the data I used in Excel:

Shift	StartTime	FinishTime
A	08:15:00	09:01:05
E	13:25:00	14:19:00
D	11:14:32	12:08:12
C	10:05:24	10:55:52
B	09:13:42	09:50:00

Table in QV:

Shift	StartTime	FinishTime	DiffStartPrevFinish
A	08:15:00	09:01:05	00:00:00
B	09:13:42	09:50:00	00:12:37
C	10:05:24	10:55:52	00:15:24
D	11:14:32	12:08:12	00:18:40
E	13:25:00	14:19:00	01:16:48

best regards,

Johan Tell, Climber

[#4]

johan,

I understood your solution, but i need to do this in the EXPRESSION, because we will use some selections and update the difference.

tks a lot!!