Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Determine if late/early in load

Hello All,

I have a table that has the following fields:

- ID,

- EmployeeId,

- ProposedStartDate,

- ProposedEndDate,

- ProposedStartTime,

- ProposedEndTime,

- ActualStartDate,

- ActualEndDate,

- ActualStartTime,

- ActualEndTime.

Example Data:

ID [1], (recordid)

EmployeeId [20154]

ProposedStartDate [05/11/2012]

ProposedEndDate [06/11/2012]

ProposedStartTime [18:30:00]

ProposedEndTime [03:30:00]

ActualStartDate [05/11/2012]

ActualEndDate [06/11/2012]

ActualStartTime [18:45:00]

ActualEndTime [03:40:00]

The load statement is simply:

LOAD *

SELECT * FROM SCHEDULE;

What I am after is two new fields to be loaded that calculate:

     1) If the employee started late.

     2) If the employee left early.

The logic for starting late (1) is probably like:

  if  [ActualStartDate & ActualStartTime] > [ProposedStartDate & ProposedStartTime] they started late else they started on time or early.

The logic for leaving early (2) is probably like:

  if  [ActualEndDate& ActualEndTime] < [ProposedEndDate & ProposedEndTime] they left early else they left on time or late.

The two new fields should only contain Yes/No if they started late/left early.

I have tried:

if(TIME(ActualStartTime,'hh:mm:ss') > TIME(ProposedStartTime,'hh:ss:ss'),'YES','NO')

But this has given me no luck and does not take into account date.

Any help would be appreciated.

Regards,

Jim

1 Solution

Accepted Solutions
Not applicable
Author

try to make timestamp for start and end time and do condition like this:

if(timestamp(timestamp#(ProposedStartDate&' '&ProposedStartTime,'dd/mm/yyyy hh:mm:ss'),'dd/mm/yyyy hh:mm:ss')  < timestamp(timestamp#(ActualStartDate&' '&ActualStartTime,'dd/mm/yyyy hh:mm:ss'),'dd/mm/yyyy hh:mm:ss'), 'started late', 'start on time') as status

View solution in original post

7 Replies
Not applicable
Author

try to make timestamp for start and end time and do condition like this:

if(timestamp(timestamp#(ProposedStartDate&' '&ProposedStartTime,'dd/mm/yyyy hh:mm:ss'),'dd/mm/yyyy hh:mm:ss')  < timestamp(timestamp#(ActualStartDate&' '&ActualStartTime,'dd/mm/yyyy hh:mm:ss'),'dd/mm/yyyy hh:mm:ss'), 'started late', 'start on time') as status

Not applicable
Author

Hello Pari Pari,

Have added the code and modified to fit with document:

if(timestamp(timestamp#(SCHEDULE.ACTSTARTDATE&' '&SCHEDULE.ACTSTARTTIME,'dd/mm/yyyy hh:mm:ss'),'dd/mm/yyyy hh:mm:ss') < timestamp(timestamp#(SWIPES.STARTDATE&' '&SWIPES.STARTTIME,'dd/mm/yyyy hh:mm:ss'),'dd/mm/yyyy hh:mm:ss'), 'started late', 'start on time') as LATEOUT2,

However, all results come back as 'start on time' even if they did not.

Any other further thoughts?

Regards,

Jim

CELAMBARASAN
Partner - Champion
Partner - Champion

Check the formats you used it should be DD/MM/YYYY hh:mm:ss which is case sensitive

Not applicable
Author

Hello Celambarasan

Yes was doing that just as you posted actually. Also, I added Time() to the attributes also. This now works which is fantastic!

Final code below (but can probably lose the TIME() surrounding the attributes).

if(timestamp(timestamp#(TIME(SCHEDULE.ACTSTARTDATE,'DD/MM/YYYY')&' '&TIME(SCHEDULE.ACTSTARTTIME,'hh:mm:ss'),'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') < timestamp(timestamp#(TIME(SWIPES.STARTDATE,'DD/MM/YYYY')&' '&TIME(SWIPES.STARTTIME,'hh:mm:ss'),'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss'), 'LATE', 'ON TIME/EARLY') as LATEIN

Thank you both.

I will be giving correct answer to Pari Pari as 95% of the code was in that post and all that was missing was the upper case and possibly the TIME() on the attributes.

Regards,

Jim

Not applicable
Author

Hello Pari Pari,

I have modified the code that you sent to include additional TIME() surrounding the variables and uppercase on the DDMMYYYY which has now made this work.

Code below:

if(timestamp(timestamp#(TIME(SCHEDULE.ACTSTARTDATE,'DD/MM/YYYY')&' '&TIME(SCHEDULE.ACTSTARTTIME,'hh:mm:ss'),'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') < timestamp(timestamp#(TIME(SWIPES.STARTDATE,'DD/MM/YYYY')&' '&TIME(SWIPES.STARTTIME,'hh:mm:ss'),'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss'), 'LATE', 'ON TIME/EARLY') as LATEIN

Thank you so much,

Jim

swuehl
MVP
MVP

I think this could be simplified:

if(SCHEDULE.ACTSTARTDATE+SCHEDULE.ACTSTARTTIME < SWIPES.STARTDATE+ SWIPES.STARTTIME, 'LATE', 'ON TIME/EARLY') as LATEIN

or as working sample:

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

ExampleData:

LOAD *,

if(ProposedStartDate + ProposedStartTime < ActualStartDate+ActualStartTime, 'LateStart','OnTime') as LateStart,

if(ProposedEndDate + ProposedEndTime < ActualEndDate+ActualEndTime, 'OnTime','EarlyLeave') as EarlyLeave

INLINE [

ID, EmployeeId, ProposedStartDate, ProposedEndDate, ProposedStartTime, ProposedEndTime, ActualStartDate, ActualEndDate, ActualStartTime, ActualEndTime

1, 20154, 05/11/2012, 06/11/2012,18:30:00,03:30:00,05/11/2012,06/11/2012,18:45:00,03:40:00

];

Not applicable
Author

Hello Swuehl

I will give this a go tomorrow morning, but I am just happy this is working currently (even though not as efficient as yours).

Thank you and I will give yours a go.

Best regards,

Jim