Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, how can I only load the the record for an employee per day with min time.
The employee clock a few times a day but I only need the first clock record.
EMPLOYEE DATE TIME
10294 2014/01/12 22:59:59.999'
10294 2014/01/12 23:05:59.999'
10774 2014/01/12 20:50:50.999'
10774 2014/01/12 20:59:57.999'
output should be
EMPLOYEE DATE TIME
10294 2014/01/12 22:59:59.999'
10774 2014/01/12 20:50:50.999'
Much Appreceated.
Update now you can check
You can use Min or Minstring functions
See the Below script
Raw:
LOAD Time(left(TIME,8),'HH:mm:ss') as TIME,EMPLOYEE,DATE;
LOAD * INLINE [
EMPLOYEE, DATE, TIME
10294, 2014/01/12, 22:59:59.999
10294, 2014/01/12, 23:05:59.999
10774, 2014/01/12, 20:50:50.999
10774 , 2014/01/12, 20:59:57.999
];
NoConcatenate
FinalTable:
LOAD
EMPLOYEE,DATE,
Time(Min(TIME),'HH:mm:ss') as Mintime
Resident Raw
Group By EMPLOYEE,DATE;
DROP Table Raw;
See the Output Snap
Update now you can check
You can use Min or Minstring functions
See the Below script
Raw:
LOAD Time(left(TIME,8),'HH:mm:ss') as TIME,EMPLOYEE,DATE;
LOAD * INLINE [
EMPLOYEE, DATE, TIME
10294, 2014/01/12, 22:59:59.999
10294, 2014/01/12, 23:05:59.999
10774, 2014/01/12, 20:50:50.999
10774 , 2014/01/12, 20:59:57.999
];
NoConcatenate
FinalTable:
LOAD
EMPLOYEE,DATE,
Time(Min(TIME),'HH:mm:ss') as Mintime
Resident Raw
Group By EMPLOYEE,DATE;
DROP Table Raw;
See the Output Snap
load EMPLOYEE,
DATE,
TIME(Min(NUM(TIME)),'hh:mm:ss') AS TIME
group by
EMPLOYEE,
DATE;
LOAD * INLINE [
EMPLOYEE, DATE, TIME
10294, 2014/01/12, 22:59:59.999
10294, 2014/01/12, 23:05:59.999
10774, 2014/01/12, 20:50:50.999
10774, 2014/01/12, 20:59:57.999
];
Hi,
If you want to solve in script then you need to use Group By clause.
LOAD EMPLOYEE,DATE, TIME(Min(NUM(TIME))) AS TIME
From YourTableName
Group By EMPLOYEE, DATE;
However, if you want to solve in UI then you need to use FirstSortedValue() function. Please see the video tutorials on my blog:
Thanks,
DV
Thanks.
Works great.