Disregarding Rows With Consecutive Data

[Potato_Ghost]

Hello Everyone! 

  I am hoping someone can give me a good answer on this. I am trying to do some reporting on our Employee In Times and There are plenty of scenariors where people do not use the FingerPrint Clock Out system correctly. This throws off our clock system.  Is there any way to account for consecutive IN scans and consecutive OUT scans? Ideally, in the case where there are consecutive INs or OUTs, I would like to keep the most rescent IN or OUT Time and disregard the others with the conditions it was the same user, and it was the same day.  I have tried plenty of different methods without much success.  Any suggestions would be greatly appreciated! 

[WolfBiber]

Hi,

can you give us some example Data as csv or table data?

[Potato_Ghost]

Hi WolfBiber! 

   Thank you very much for your response.  Please find attacehd a CSV of what I have to work with it. 

[Potato_Ghost]

You May Download Here

[Ashish_Mathur]

Hi,

Based on the data that you have shared, please show the exact result which you are expecting.

[Potato_Ghost]

Hi Ashish! 

   I apologize for not being clear.  I am having trouble explaining exactly what I want to happen.  The picture belows depicts a sample of the dataset I have.  I went ahead and pulled out all the time entries I have for one user on one day and sorted by earliest time to latest time.  You can see that he clocked in and out of the building then clocked out twice in a row.  He must have scanned his finger to record the out entry, then forgot something, did not leave the building, then grabbed what he wanted then scanned out of the building again so that the door would open for him, and left. This causes my total hours to sum incorrectly. A IN time entry should be followed by an OUT entry, and on the flip side an OUT entry should be followed by an IN entry. Is there a way to make a measure or Calculated column that says IF a unique user has the same IN/OUT value as the previous record, then the earlier entry that is the same has a value of 0 in the total hour column, else just total hours value? I know that is confusing but I hope that summed it up ok.  Thank you so very much for taking a look.  

[Ashish_Mathur]

Hi,

Can we use this log - If the Date, User ID and Door combination are the same as the previous row, then mark a 1 in that row?  Please let me know.

[Potato_Ghost]

Yes! That sounds great! The door column should not be considered though since employees can enter and exit the same door. 

We would want to say If Date, UserID, and IN/OUT are the same as the previous row then mark a 1 in that row. 

[WolfBiber]

Hi, 

there is a simple and a more compley way to archive that.

If you just want to know if everybody loggedoff, you can make a compare (measure): 

CountINs = CALCULATE(COUNT(AttendanceSampleData[IN/OUT]);AttendanceSampleData[IN/OUT]="IN")
CountOUTs = CALCULATE(COUNT(AttendanceSampleData[IN/OUT]);AttendanceSampleData[IN/OUT]="OUT")
off = if(IF(ISBLANK([CountOUTs]);0;mod([CountINs];[CountOUTs]))=0;0;1)

Or as Calc Column something like

UserLoggedOut = CALCULATE(MAXX(AttendanceSampleData;AttendanceSampleData[IN/OUT]);AttendanceSampleData[Time]< EARLIER(AttendanceSampleData[Time]);AttendanceSampleData[userid]=EARLIER(AttendanceSampleData[userid]))

be aware: very CPU/Mem consuming.

Sorry I didnt had time to test it.

Please tell us your goal: Analysis or cleansing?