Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

choose starting time

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

4 Replies
balrajahlawat
Esteemed Contributor

Re: choose starting time

Try with FirstValue(), like?

Load *,

FirstValue(TimeField) as [First Entered Time]

from TableName group by Person;

Not applicable

Re: choose starting time

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

balrajahlawat
Esteemed Contributor

Re: choose starting time

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

Not applicable

Re: choose starting time

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)