load first min time record per employee per date

[#1]

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.

[its_anandrjs · Accepted solution]

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

[#3]

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

];

[IAMDV]

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:

http://qlikshare.com/392/

http://qlikshare.com/398/

Thanks,

DV

[#5]

Thanks.

Works great.