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

SUMIF and COUNTIF across separate data files.

Bdethloff
5 - Atom

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_Date
072150332011-10-072011-12-09
072150352012-06-142012-10-29
072150362012-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 ID

033

2011-10-17.5012
0332011-10-17.52012
0332012-06-20.43011

 

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

 

3 REPLIES 3
sjm
8 - Asteroid

@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.

Bdethloff
5 - Atom

Very smart, thank you. 

Bdethloff
5 - Atom

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. 

 

TruckDateHours DrivenDriver ID
03312/13/16252
03312/14/16252
05015/3/17  

 

SerialTruckStart Stop
0721503312/12/1614/2/17
0721505015/3/1710/10/17
072150513/3/1511/13/16
0774403312/12/1614/2/17
077440522/4/1619/5/16
077440501/4/1712/12/17
Labels