Joining data in chronological order

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!

Device Workflow.yxmd

Dynamic Processing

Join

Accepted answers

All comments

binu_acs

@bgoshorn please also provide the two input files

bgoshorn

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

Device out-in.xlsx

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.

binu_acs

@bgoshorn are you looking for something like this?

Device Workflow.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703