Forum Discussion
Managing relationships between datasets with date variable
clean up your data and create a data model
Adding injuries to that chart will create cognitive overload. Use separate visuals.
Read about how to handle SCD2 - Slowly Changing Dimension Type 2
Your tables are not coherent enough. The date format is all over the palceand is missing the year, the Pen ID is missing from the tables, and the temperature dates have no overlap with the injury dates. The start date is missing for the Pen table.
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
Hello and apologies for the late response. I have amended the tables. Please see sample data for weight of feed supplied between July and August for two pens, A1 and A2, below.
| Date | A1 | A2 |
| 2024-07-21 | 0 | 0 |
| 2024-07-22 | 2,400 | 800 |
| 2024-07-23 | 0 | 0 |
| 2024-07-24 | 3,200 | 2,400 |
| 2024-07-25 | 4,800 | 4,000 |
| 2024-07-26 | 6,400 | 4,000 |
| 2024-07-27 | 8,000 | 5,600 |
Below, the weight records for injured individuals from two sample pens A1 and A2 during the same period.
| Date | A1 Injured individual weight |
| 2024-07-21 | 150 |
| 2024-07-21 | 150 |
| 2024-07-22 | 240 |
| 2024-07-23 | 25 |
| 2024-07-24 | 200 |
| 2024-07-24 | 200 |
| 2024-07-25 | 180 |
| 2024-07-25 | 150 |
| 2024-07-25 | 150 |
| 2024-07-26 | 120 |
| 2024-07-27 | 160 |
| 2024-07-27 | 160 |
| 2024-07-27 | 160 |
| Date | A2 Injured individual weight |
| 2024-07-22 | 180 |
| 2024-07-22 | 50 |
| 2024-07-23 | 150 |
| 2024-07-23 | 150 |
| 2024-07-23 | 100 |
| 2024-07-24 | 50 |
| 2024-07-24 | 50 |
| 2024-07-24 | 190 |
| 2024-07-24 | 190 |
| 2024-07-24 | 190 |
| 2024-07-24 | 190 |
| 2024-07-24 | 190 |
| 2024-07-25 | 250 |
| 2024-07-25 | 250 |
| 2024-07-25 | 250 |
| 2024-07-25 | 250 |
| 2024-07-25 | 50 |
| 2024-07-27 | 250 |
Below is the temperature data for the same period.
| Date | Time | Temp |
| 2024-07-21 | 00:00:00 | 23.9938 |
| 2024-07-21 | 12:00:00 | 24.0136 |
| 2024-07-22 | 00:00:00 | 24.0335 |
| 2024-07-22 | 12:00:00 | 24.0534 |
| 2024-07-23 | 00:00:00 | 24.0733 |
| 2024-07-23 | 12:00:00 | 24.0931 |
| 2024-07-24 | 00:00:00 | 24.113 |
| 2024-07-24 | 12:00:00 | 24.1329 |
| 2024-07-25 | 00:00:00 | 24.1527 |
| 2024-07-25 | 12:00:00 | 24.1726 |
| 2024-07-26 | 00:00:00 | 24.1925 |
| 2024-07-26 | 12:00:00 | 24.2124 |
| 2024-07-27 | 00:00:00 | 24.871 |
| 2024-07-27 | 12:00:00 | 25.841 |
| Cage Number | Start Date | Start Number | Start Weight | Start Average | Origin |
| A1 | 2024-07-21 | 1000 | 10000 | 10 | UK |
| A2 | 2024-07-22 | 999 | 12000 | 12 | UK |
I want to have plots similar to the below to compare feeding and/or injuries across multiple pens, and to which I can add the temperature profile. Eventually I will want to include variables like 'Origin' to compare 'Injuries' between pens with different origin.
I hope this clarifies and I appreciate your help! Thank you.