4 Replies Latest reply: Feb 17, 2016 11:45 AM by Andrew Mein RSS

    choose starting time

    mani kanta

      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

       

       

      Untitled1.png

       

      thanks,

      mani

        • Re: choose starting time
          balraj ahlawat

          Try with FirstValue(), like?

           

          Load *,

          FirstValue(TimeField) as [First Entered Time]

          from TableName group by Person;

          • Re: choose starting time
            mani kanta

            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

              • Re: choose starting time
                balraj ahlawat

                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

              • Re: choose starting time
                Andrew Mein

                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)