cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
OzzieFrog Member
Member

Impossible Data Transformation

Hi folks,

 

I have a curvy one and can't even figure out on paper how to transform this so here is hoping:

 

I've got a table which contains swipecard information per employee. My goal is to identify how many hours were spent at a site/location for each employee and then roll it up at various levels.

 

The problem that I have is that one employee may do multiple swipes a day, at the entrance and throughout the locations. I am able to identify when a swipe is done at an entrance/exit through the relevant columns but have the following problems:

  . an employee may swipe to come in (ie to let someone in) or go out multiple times in a row

  . an employee may not swipe in as he was let in or may not swipe out (in this event, I would assume the time spent at site is 8 hours)

  . an employee may work night and start in the evening and finish in the morning.

 

I am not able to attach a pbi file (am on company computer and can't load my personal OneDrive) but below is an extract the data. I assume I need to transform and transpose the data against a date table but have no idea where to start.

 

CompanyCard #SwipeDateTimeSwipe PointMain Entry FlagMain Exit Flag
A7121/01/2019 8:00RANDOMYESYES
A7121/01/2019 10:00RANDOMYESNO
A7121/01/2019 18:00RANDOMNOYES
A82163/01/2019 8:00RANDOMYESNO
A723/01/2019 9:00RANDOMYESNO
A82163/01/2019 10:00RANDOMNONO
A82163/01/2019 19:00RANDOMNOYES
A724/01/2019 2:00RANDOMYESNO
B87835/01/2019 8:00RANDOMYESNO
B87835/01/2019 8:01RANDOMYESNO
B87835/01/2019 11:00RANDOMNOYES
B28366/01/2019 22:00RANDOMYESNO
B28367/01/2019 1:00RANDOMNONO
B28367/01/2019 2:00RANDOMNONO
B28367/01/2019 4:00RANDOMNONO
B28367/01/2019 6:00RANDOMNOYES

 

Any thoughts or guidance will be greatly appreciated 🙂

 

OF

1 ACCEPTED SOLUTION

Accepted Solutions
vanessafvg Super Contributor
Super Contributor

Re: Impossible Data Transformation

@OzzieFrog  what are your requirements to get the first and last?  what flags are you using?  you need to give a bit more information about the rules you want to develop.

 

Do you want the first and last for each day, how do you know if they worked over night?  etc..


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!

View solution in original post

3 REPLIES 3
vanessafvg Super Contributor
Super Contributor

Re: Impossible Data Transformation

@OzzieFrog  what are your requirements to get the first and last?  what flags are you using?  you need to give a bit more information about the rules you want to develop.

 

Do you want the first and last for each day, how do you know if they worked over night?  etc..


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!

View solution in original post

OzzieFrog Member
Member

Re: Impossible Data Transformation

@vanessafvg, apologies, I incorrectly marked this as solved.

 

I'm trying to calculate the numbers of hour per employee, per day, spent at site (if they worked overnight, just number of hours spent there from one evening to th enext morning). The two "tricky parts" are:

 

. an employee may swipe in twice (only the first one should count)

. an employee may not swipe in and only swipe out, in which case we can assume the swipe in was 8 hours earlier.

 

Did I explain this correctly?

vanessafvg Super Contributor
Super Contributor

Re: Impossible Data Transformation

@OzzieFrog  dd you get this resolved?


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 44 members 857 guests
Please welcome our newest community members: