Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Join data from two files in sets of two records: departure data - arrival data

dla
6 - Meteoroid

Dear Alteryx Community Members,

I hope that a kind soul could help me out with a piece of advice, as it seems that I have gotten myself into deep waters. I have used Alteryx for 3 months now.

 

I need to figure out how to solve a project like this:

 

Scenario: An employee scans the barcode of a bus stop before getting on a bus. The barcode scanner also registers the GPS location, number of the bus line, the employee ID, trip  ID, date and timestamp. When the employee gets off the bus, he scans the barcode of the bus stop at the destination.

 

The two barcode scans that are registered come in two separate files, and I need to join these data in pairs, giving that we should always have a set consisting of data from departure bus stop and data from arrival bus stop.

 

File #1 – Departure: Getting on the bus

Employee ID, ride ID, gps location, bus route number, bus number, type=”departure”, date, timestamp

 

File #2 – Arrival: Getting off the bus

Employee ID, ride ID, gps location, type=”arrival”, date, timestamp

 

I need to join on Employee ID, trip ID and date. The employee enters the employee  ID and trip ID manually. So this could lead to wrong numbers, which I need to deal with.

The timestamp of type=”departure” should always precede the timestamp of type=“arrival”.

In case of more matches in File #2 (in case the employee enters duplicate trip IDs), how can I join with just the first match?

 

Any advice, links or suggestions on how to deal with this are most welcome.

Best regards,

Dorte

2 REPLIES 2
jdunkerley79
ACE Emeritus
ACE Emeritus

I would suggest you use a sample tool to pick the first record of each EmployeeID and RideID from File #2 before joining to File #1.

 

 Something like:

2017-05-23_09-19-13.jpg

dla
6 - Meteoroid

Thank you very much for this suggestion. This might be enough to do the job. It's not complete yet, so time will show. Thanks again.

Labels