choose starting time

[#1]

Hi

I want to select the first entered time of the of day for each person and in names column some cell data are empty ,i want to fill those cells with above row cell data of each empty cell .

like below data

thanks,

mani

[Anonymous]

Try with FirstValue(), like?

Load *,

FirstValue(TimeField) as [First Entered Time]

from TableName group by Person;

[#3]

Hi Balraj,

It's working but the data arranged in zigzag format ,i want to pic the min and max time in a day ,i tried with with min and max functions but those are not working .i think time stamp is required for selecting min and max results in a time formate .is there any way to get it.

thanks,

mani

[Anonymous]

As you mentioned i want to pic the min and max time in a day ...

Do the grouping by Day, like this?

Load Day,

min(TimeField) as [Min Time],                           //Use Num#() if required

max(TimeField) as [Max Time]

Resident MainTableName group by Day;

I think this should work

[#5]

It looks like your data is in two parts - NBS0710 etc. looks to be an employee ID - creating a table with this ID and name, will let you fill the missing cells with the name.

The date and times can then be imported with just the ID.

Convert to date and time values using:

DATE#([DateColumn],'DD/MMM/YYYY') AS Log_Date

TIME#([TimeColumn],'hh:mm:ss') AS Log_Time

You can then create a column as (Log_Date + Log_Time) AS Log_DateTime and do a MIN/MAX function on it.

Load script would be something like:

LOAD

     EmployeeID,

     EmployeeName,

     Department

FROM Excel_File

WHERE EmployeeName <> '-';

LOAD

     *,

     Log_Date + Log_Time AS Log_DateTime;

LOAD

     EmployeeID,

     DATE#([DateColumn],'DD/MMM/YYYY') AS Log_Date,

     TIME#([TimeColumn],'hh:mm:ss') AS Log_Time

FROM Excel_File;

Then your table could be:

EmployeeID, EmployeeName, Department, MIN(Log_DateTime)