Location tracker based on time

[udles5666]

Hi Community,

Can you please help me understand how can I filter out on Min/Max time  based on locations. Please see the scenario below and find attached excel and package workflow for reference. Thanks in advance. 

Scenario: 

There are only two entrance doors a person can use to get into the premises. (HMGBR5_Main_IN & HMGBR5_SNEAK_IN). Similarly, there only two exit doors. (HMGBR5_Main_OUT & HMGBR5_SNEAK_OUT). 

• Batman - Batman is a shift worker start in the middle of the night and finished in the morning. Even though he used SNEAK_IN & MAIN_IN doors but his Start Time will be 12:22:57 a.m. for 13th Sept 2020 as he used that does to come in at the first instance on that day. His finish time is  9:59:55 a.m.
• Charlie - Charlie is a shift pre-grave shift worker starts at 16:16:15 p.m. on 13th September 2020 and continues to work until next morning. But, technically he did not finish on 13th Sep 2020. So, below is the desired outcome for him. 

Desired output: 

One card number/name per row per day as per desired output tab in excel

[grazitti_sapna]

HI @udles5666 ,refer to the attached workflow.

Thanks.

[udles5666]

Hi @grazitti_sapna 

I went through the solution but it does not look right. 

The data looked all right until Crosstab (27). I tried swapping start time and finish time at the end select but that didn't work/

Screenshots attached for reference.  

Can you please have a look again? Thanks

The End Result should be ass per below for example:

Potential Case Card Number	Potential Case Name	Company	Case Date	Start Time	Finish Time	Entry Gate	Exit Gate
59960	Charlie	Holly Molly Guacamoley Brands (AU) Ltd	13/09/2020	16:16:15 p.m.	null/blank	HMGBR5_Main_IN	null/blank
59994	Batman	Holly Molly Guacamoley Brands (AU) Ltd	13/09/2020	12:22:57 a.m.	9:59:55 a.m.	HMGBR5_SNEAK_IN	HMGBR5_Main_OUT

[udles5666]

Hi @grazitti_sapna 

I had a look at the solution but the end result do not match. 

The data looked fine until crosstab (27) but after text to columns it doesn't match the expected outcome. 

I tried swapping Start & Finish time in the end SELECT tool but that was also giving me incorrect results. 

Can you please have a look again?

Thanks,

[grazitti_sapna]

Hi @udles5666 , I tired fixing the issue. Please test it.

Thanks.

[udles5666]

Hi @grazitti_sapna 

Many thanks again and much appreciated. 

The issue looks fixed for Batman & Charlie. But when I look at Taylor, it looks weird. 

Since Taylor did not arrive through either of Main_IN or Sneak_In door the desired output for Entry should be Null just like Charlie has null for exit gate and finish time. 

Attached screenshots for reference. Thanks again.  

[grazitti_sapna]

Hi @udles5666 , fixed please check.

Thanks.

[udles5666]

So sorry to be a pain!

But now it looks like data has gone missing. Example Darren - Came in on 12th Sept but data is not there 😞 

Attached for reference. 

[grazitti_sapna · Accepted solution]

@udles5666 , check now.

Thanks.