I am having trouble joining two data sources for linear regression... I am trying to combine tire maintenance data with daily production data from the truck on which the tire was found. In Excel terms, I need to perform SUMIF and COUNTIF from two separate data sources. My first data source is grouped by the serial number of the tire, then by the truck on which the tire was recorded, I can also see the date range during which the tire was on the truck. It looks like this... Serial NumberTruckMin_DateMax_Date072150332011-10-072011-12-09072150352012-06-142012-10-29072150362012-04-222012-05-02 My second data source is production data, where individual activities (dozens per day) are captured and organized by truck. It looks like this... TruckDateHours DrivenOperator ID033 2011-10-17.50120332011-10-17.520120332012-06-20.43011 I need to simultaneously join these two files and Sum/Count the corresponding production data according to the recorded maintenance data.

SUMIF and COUNTIF across separate data files.

I am having trouble joining two data sources for linear regression...

I am trying to combine tire maintenance data with daily production data from the truck on which the tire was found.

In Excel terms, I need to perform SUMIF and COUNTIF from two separate data sources.

My first data source is grouped by the serial number of the tire, then by the truck on which the tire was recorded, I can also see the date range during which the tire was on the truck. It looks like this...

Serial Number	Truck	Min_Date	Max_Date
07215	033	2011-10-07	2011-12-09
07215	035	2012-06-14	2012-10-29
07215	036	2012-04-22	2012-05-02

My second data source is production data, where individual activities (dozens per day) are captured and organized by truck. It looks like this...

Truck	Date	Hours Driven	Operator ID
033	2011-10-17	.5	012
033	2011-10-17	.52	012
033	2012-06-20	.43	011

I need to simultaneously join these two files and Sum/Count the corresponding production data according to the recorded maintenance data.

Join

Preparation

Predictive Analysis

Accepted answers

sjm

@Bdethloff I may not be fully understanding what the output should look like, but a potential solution is attached. It seems that we can simply join the two tables using the Truck ID. And then use the Summarize tool to sum hours by truck, serial #, date, driver.

Not sure if you need any checks in place to make sure the production data falls within the dates ranges in the first table. The last record in the second table wasn't within the date ranges. Anyway, I built in a check.

trucks and tires.yxmd

All comments

sjm

trucks and tires.yxmd

Bdethloff

Very smart, thank you.

Bdethloff

Sorry. One quick follow-up. The join isn't working for me because I have thousands of serial numbers assigned to the same truck. So, it would be more like this... I have two tyres (indicated by their serial numbers) who were on truck 33 at the same time... In need the production data from Truck 33 for every applicable date to be assigned to those two tyres.

Truck	Date	Hours Driven	Driver ID
033	12/13/16	2	52
033	12/14/16	2	52
050	15/3/17

Serial	Truck	Start	Stop
07215	033	12/12/16	14/2/17
07215	050	15/3/17	10/10/17
07215	051	3/3/15	11/13/16
07744	033	12/12/16	14/2/17
07744	052	2/4/16	19/5/16
07744	050	1/4/17	12/12/17

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703