Joining data in chronological order

[bgoshorn]

Hello,

I am trying to generate a dynamic report of checked out laptops based on check-in and check-out time. I have been using the join tool to find matching device numbers that appear in both the check out and check in data. The devices that appear in the left output of the join tool are the devices that are currently checked out and have not been returned yet. However it is possible that a single device is checked out multiple times in one day, creating duplicate device numbers in the data. This is where i run into an issue. See record 3 below, it joined a check out time of 13:00 with a return time of 09:00, which of course is not physically possible. Is there a way that the device # data being joined can appear in chronological order? so that a matching device number is only joined if the time on the right data is greater than the left?

Thanks in advance!

[binuacs]

@bgoshorn please also provide the two input files

[bgoshorn]

See below. Sheet 1 is the check out and sheet 2 is check in.

[CathyS_Slalom]

Hi @bgoshorn, you can add a filter after the join to have the result that you wanted. Please let me know if this is not the end results that you are looking for.

[bgoshorn]

Hi there @cathyshi525, 

Not quite what i am looking for. I want the join tool to only join records with matching device numbers IF the date on the left record < right record. But the device number matches but the left record > right record, would want those record to fall into the left or right output and not the join output.

[binuacs]

@bgoshorn are you looking for something like this?

[CathyS_Slalom]

@bgoshorn I don't think you can do that before the join, so you would need to filter out the data after the join to make sure only the check in time is less than check out time to flow into your next steps. Also one note, if a person could check out the next day, it is better to acquire the date column from the source.

[CathyS_Slalom]

Also, those records are not meet the "check in time < check out time" criteria will be under the "F" anchor on the filter tool. Let me know if this resolves your problem.