Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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 !!
The type will not matter really, as long as I have StartTime and EndTime
Always only 2 lines for each user
A is for Add and D is for Delete, basically represents the start and end of a transaction.
Thanks
Al.
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;
Thank You