reduce data from 2 lines per record to 1

[rustyfishbones]

Hi All,

I have the following Data

User      Type     LineNumber  time     trxno

1             A                1             0700       1

1             D               2             0800        1

I want to output to

User      Type     StartTime  EndTime trxno

1             A              0700       0800        1

Whats the best solution to do this?

[MK_QSL]

You want to remove Type D also... I mean the Minimum Time Type will be considered?

Also, do you have only two line number or for other users this line number may vary !!

[rustyfishbones]

The type will not matter really, as long as I have StartTime and EndTime

Always only 2 lines for each user

[rustyfishbones]

A is for Add and D is for Delete, basically represents the start and end of a transaction.

Thanks

Al.

[MK_QSL · Accepted solution]

SET TimeFormat='hhmm';

Data:
LOAD User, 
     Type, 
     LineNumber, 
     Time#(time,'hhmm') as time, 
     trxno
FROM
[Community_296628.xlsx]
(ooxml, embedded labels, table is Sheet1);

Right Join
Load User, Min(time) as time, Time(Min(time)) as StartTime, Time(Max(time)) as EndTime Resident Data Group By User;

Drop Fields time, LineNumber;

[rustyfishbones]

Thank You