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
Thank you.
Please see samples of datasets below that I would like to create relationships for. I have different records for different dates, and relating records across different datasets for corresponding dates for each pen, then between different pens, is what I am after. Not sure if this clarifies..
Feeding dataset for one of the pens:
| Date | Feed weight |
| Sun 07 Jul | 0 |
| Mon 08 Jul | 0 |
| Tue 09 Jul | 0 |
| Wed 10 Jul | 480 |
| Thu 11 Jul | 960 |
| Fri 12 Jul | 960 |
| Sat 13 Jul | 1,200 |
| Sun 14 Jul | 0 |
| Mon 15 Jul | 0 |
| Tue 16 Jul | 800 |
| Wed 17 Jul | 2,000 |
Injured weight records for one of the pens:
| Date | Injured ind. weight |
| 06-Jul | 130 |
| 07-Jul | 200 |
| 10-Jul | 170 |
| 11-Jul | 50 |
| 12-Jul | 150 |
| 13-Jul | 200 |
| 13-Jul | 200 |
| 14-Jul | 220 |
| 15-Jul | 200 |
| 15-Jul | 200 |
| 18-Jul | 150 |
| 18-Jul | 80 |
Temperature dataset:
| 30/06/2024 19:00 | 25.113 |
| 30/06/2024 20:00 | 25.21 |
| 30/06/2024 21:00 | 25.453 |
| 30/06/2024 22:00 | 25.283 |
| 30/06/2024 23:00 | 25.331 |
| 01/07/2024 00:00 | 24.968 |
| 01/07/2024 01:00 | 25.283 |
| 01/07/2024 02:00 | 24.895 |
Dimension table:
| Pen Number | Start Number | Start Weight | Start Average | Origin |
| A1 | 1000 | 10000 | 10 | UK |
| A2 | 999 | 12000 | 12 | UK |
| A3 | 1200 | 11000 | 9 | US |
| A4 | 1300 | 10000 | 8 | US |
| A5 | 1010 | 10000 | 10 | ITA |
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.