Help with Table

[rustyfishbones]

Hi All,

I have the following table of Data

adjustment-type date-added line-number Time-added trx-no trx-type

A                        30/08/2016        1                0724           1        Z2

D                        30/08/2016        2                1314           1        Z2

But I want to use the above table to create the below table

date-added line-number Time-added trx-no trx-type Time-Stopped time-diff

30/08/2016         1               0724            1       Z2      1314                   350

I was wondering can someone can suggest the best way to do it.

Thanks

Alan.

[antoniotiman · Accepted solution]

Hi Alan,

maybe this

Temp:
LOAD adjustment_type,date_added,line_number,
Time(Time#(Time_added,'hhmm'),'hhmm') as Time_added,trx_no,trx_type Inline [
adjustment_type, date_added, line_number, Time_added, trx_no, trx_type
A , 30/08/2016, 1, 0724, 1, Z2
D , 30/08/2016, 2, 1314, 1, Z2];
Temp1:
LOAD *,Interval(Time_stopped-Time_added,'mm') as time_diff;
LOAD date_added,line_number,Time_added,trx_no,trx_type,
If(date_added=Peek(date_added) and trx_no=Peek(trx_no) and trx_type=Peek(trx_type),Peek(Time_added)) as Time_stopped
Resident Temp
Order By trx_type,trx_no,date_added,line_number desc;
NoConcatenate LOAD * Resident Temp1 Where line_number = 1;
Drop Tables Temp,Temp1;

Regards,

Antonio

[rustyfishbones]

Perfect thanks Antonio, I was using PEEK but not using Order By