I have the following two sources of data :
1- System
2- Manual Records
The system records tell me the time travel, fuel used and refuel for vehicles movement day-wise whereas the Manual records tell me the start and end points and dates for these vehicles movements. The system records have the data for productive trips (sales) and non-productive trips (all except sales e.g. maintenance , servicing , to/ from parking etc.).
The problem that I am having is that I want to combine these two data streams i.e. System and Manual records to tap-out some meaningful information about complete trip movements from one location to another. Wondering how I can tag/pair/lookup the productive trips start and end point (from manual records) into the system records so that I may know how much fuel the vehicle consumed per trip (according to location for complete trip). i tried combining the date & vehicle # (concatenate) to create the a unique-ID and look-up the start/end points but it only gives me the lookup values for the two dates i.e. start date and end date and returns blank for all the date in-between e.g. If i look up the unique-ID for a trip from Location 1 to destination 1 duration of which was 4 days starting from 1st July-4th July, I get the lookup values for 1st and 4th July only.
Can some please suggest me the way around here, Data file attached for reference .. Thanks in advance
Bookmarks