Production Lot Upload Data Preparation: Coaldale Veterinary Clinic (CVC)
Site Information Details
Coaldale Veterinary Clinic (3) (CVC)Vet: CSBV00004 (4)
Project Year: 2019
The site has four (4) herds. They are:
CSBH0000030
CSBH0000031
CSBH0000032
CSBH0000033
Access to current years documentation click on the appropriate link below:
CVC 2020
CVC 2021
CVC 2022
CVC 2023
Production Lot Upload Data Preparation: Coaldale Veterinary Clinic (CVC)
Data Source Information
CVC has 4 herds.Each spreadsheet/workbook from CVC represents all herds for a specific sampling year.
The core fields in a sheet have drug fields listed at the end of the core field data.
The drugs are listed as rows, not columns, within the sheet.
Core field data is repeated for each associated row of drug information.
However, the repeated core field data is implied, meaning core field information has 1 row filled in,
the remaining rows are blank until you switch to a different production lot.
During the upload of the spreadsheet into the database, missing or implied core field data is filled in.
The rows of drug data represent 1 drug, usually a product name and active ingredient for 1 drug route.
All drug routes and associated drugs for a production lot are available.
There are several extra things you should do in the spreadsheet before uploading the data:
The season field is not added and needs to be calculated.
Define season using the Average Date Out field in the following way:
Winter -> January 1 thru March 31
Spring -> April 1 thru June 30
Summer -> July 1 thru September 30
Fall -> October 1 thru December 31
The days on feed field needs to be calculated.
Define days on feed -> Average Date Out minus (-) Average Date In
Before creating and setting up a template, go thru all spreadsheets provided from the site.
Collect field names from each site and put them together. Review the field names to make sure they all
match. My experience is the field names do not match. To reduce the number of templates to create,
get the field names to match. The fields do not need to be in the same order.
Production Lot Upload Data Preparation: Coaldale Veterinary Clinic (CVC)
Template Setup Suggestions
A two template setup is used for one or more years batch of data.For the current document, assume we place all known drug fields for all drug routes at the end of the core fields.
Since the data switches from Core field columns to Drug fields as rows we insert a field in the spreadsheet
The field is called "change_to_rows". It is a column header in the spreadsheet and a field name in the uploaded data table.
When we do this, the primary, top level template created represents the Core fields.
The secondary, second level child template created represents the Drug fields associated with a Core row of data.
The Core Template is populated from the Target Table from the production lot table (core fields).
The Child Drug Template is populated from the Target Table from the drug table.
Here are the typical template settings used for CVC.
Name: CVC 2019 - Core
Level: Top Level
Type: Core
Route: Multiple Drugs
Version: Version 1
By Row or Column: By Column
Method: Populate template fields from Target
|
||||||
|---|---|---|---|---|---|---|
| column_number | column_not_used | target_field_category | target_column_name | target_column_type | target_column_classify_field_ |
source_column_name |
| 1 | Not Used (1) | Core Field (0) | csb_survey_production_lot_id | bigint | Setup Standard Field (1) | csb_survey_production_lot_id |
| 2 | Not Used (1) | Core Field (0) | csb_survey_main_id | bigint | Setup Standard Field (1) | csb_survey_main_id |
| 3 | Not Used (1) | Core Field (0) | csb_record_group_id | bigint | Setup Standard Field (1) | csb_record_group_id |
| 4 | Not Used (1) | Core Field (0) | csb_account_id | bigint | Setup Standard Field (1) | csb_account_id |
| 5 | Not Used (1) | Core Field (0) | csb_survey_production_lot_ |
bigint | Setup Standard Field (1) | csb_survey_production_lot_ |
| 6 | Not Used (1) | Core Field (0) | csb_pl_template_id | integer | Setup Standard Field (1) | csb_pl_template_id |
| 7 | Used (0) | Core Field (0) | csb_pl_production_lot_code | varchar(128) | Setup Standard Field (1) | feedlot_lot_name |
| 8 | Used (0) | Core Field (0) | csb_pl_sex_id | integer | Setup Lookup Field (2) | sex |
| 9 | Used (0) | Core Field (0) | csb_pl_animal_age_category_id | integer | Setup Lookup Field (2) | cattle_type |
| 10 | Used (0) | Core Field (0) | csb_pl_percent_calves | numeric | Setup Standard Field (1) | percent_calves |
| 11 | Used (0) | Core Field (0) | csb_pl_percent_yearlings | numeric | Setup Standard Field (1) | percent_yearlings |
| 12 | Used (0) | Core Field (0) | csb_pl_origin_id | integer | Setup Lookup Field (2) | origin |
| 13 | Used (0) | Core Field (0) | csb_pl_brd_risk_id | integer | Setup Lookup Field (2) | brd_risk |
| 14 | Used (0) | Core Field (0) | csb_pl_n_animals_in | integer | Setup Standard Field (1) | hd_in |
| 15 | Used (0) | Core Field (0) | csb_pl_avg_date_in | date | Setup Standard Field (1) | avg_date_in |
| 16 | Not Used (1) | Core Field (0) | csb_pl_source_weight_in_units_ |
integer | Setup Lookup Field (2) | csb_pl_source_weight_in_units_ |
| 17 | Not Used (1) | Core Field (0) | csb_pl_source_weight_in_value | numeric | Setup Standard Field (1) | csb_pl_source_weight_in_value |
| 18 | Used (0) | Core Field (0) | csb_pl_avg_weight_in_kgs | numeric | Setup Multiple Target Fields (3) | avg_wt_in |
| 19 | Used (0) | Core Field (0) | csb_pl_avg_date_out | date | Setup Standard Field (1) | avg_date_out |
| 20 | Used (0) | Core Field (0) | csb_pl_quarter_of_year_ |
integer | Setup Lookup Field (2) | season |
| 21 | Not Used (1) | Core Field (0) | csb_pl_source_weight_out_ |
integer | Setup Lookup Field (2) | csb_pl_source_weight_out_ |
| 22 | Not Used (1) | Core Field (0) | csb_pl_source_weight_out_value | numeric | Setup Standard Field (1) | csb_pl_source_weight_out_value |
| 23 | Used (0) | Core Field (0) | csb_pl_avg_weight_out_kgs | numeric | Setup Multiple Target Fields (3) | avg_wt_out |
| 24 | Used (0) | Core Field (0) | csb_pl_n_animals_dead | integer | Setup Standard Field (1) | deaths |
| 25 | Used (0) | Core Field (0) | csb_pl_n_animals_slaughtered | integer | Setup Standard Field (1) | hd_out |
| 26 | Used (0) | Core Field (0) | csb_pl_total_days_on_feed | numeric | Setup Standard Field (1) | days_on_feed |
| 27 | Used (0) | Core Field (0) | csb_pl_total_head_days | numeric | Setup Standard Field (1) | hddays |
Name: CVC 2019 - Drugs
Level: Second Level
Type: Drug
Route: Multiple Drugs
Version: Version 1
By Row or Column: By Row
Method: Populate template fields from Target
|
|||||||||
|---|---|---|---|---|---|---|---|---|---|
| column_number | column_not_used | source_column_name | source_column_type | target_field_category | target_column_name | target_column_type | target_column_classify_field_setup | ||
| 1 | Not Used (1) | csb_survey_production_lot_drug_id | bigint | Core Field (0) | csb_survey_production_lot_drug_id | bigint | Setup Standard Field (1) | ||
| 2 | Not Used (1) | csb_survey_production_lot_id | bigint | Core Field (0) | csb_survey_production_lot_id | bigint | Setup Standard Field (1) | ||
| 3 | Not Used (1) | csb_survey_main_id | bigint | Core Field (0) | csb_survey_main_id | bigint | Setup Standard Field (1) | ||
| 4 | Not Used (1) | csb_record_group_id | bigint | Core Field (0) | csb_record_group_id | bigint | Setup Standard Field (1) | ||
| 5 | Not Used (1) | csb_account_id | bigint | Core Field (0) | csb_account_id | bigint | Setup Standard Field (1) | ||
| 6 | Not Used (1) | csb_survey_production_lot_upload_id | bigint | Core Field (0) | csb_survey_production_lot_upload_id | bigint | Setup Standard Field (1) | ||
| 7 | Not Used (1) | csb_pl_template_id | integer | Core Field (0) | csb_pl_template_id | integer | Setup Standard Field (1) | ||
| 8 | Not Used (1) | csb_pl_drug_ordinal | integer | Core Field (0) | csb_pl_drug_ordinal | integer | Setup Standard Field (1) | ||
| 9 | Used (0) | route | integer | Core Field (0) | csb_pl_drug_route_id | integer | Setup Lookup Field (2) | ||
| 10 | Used (0) | active_ingredient | varchar (192) | Core Field (0) | csb_pl_drug_source_name | varchar (192) | Setup Standard Field (0) | ||
| 11 | Used (0) | amount | numeric | Core Field (0) | csb_pl_source_drug_value | numeric | Setup Standard Field (1) | ||
| 12 | Used (0) | units | integer | Core Field (0) | csb_pl_source_drug_value_units_id | integer | Setup Lookup Field (2) | ||
| 13 | Used (0) | product | integer | Trade Name (2) | csb_pl_drug_trade_name_id | integer | Setup Lookup Field (2) | ||
| 14 | Used (0) | active_ingredient | integer | Active Ingredient (1) | csb_pl_drug_active_ingredient_id | integer | Setup Lookup Field (2) | ||
| 15 | Not Used (1) | amount | numeric | Core Field (0) | csb_pl_drug_total_mg | numeric | Setup Standard Field (1) | ||
| 16 | Not Used (1) | csb_pl_drug_total_ml | numeric | Core Field (0) | csb_pl_drug_total_ml | numeric | Setup Standard Field (1) | ||
| 17 | Used (0) | reason_for_use | integer | Core Field (0) | csb_pl_drug_primary_reason_for_use | integer | Setup Lookup Field (2) | ||
| 18 | Used (0) | condition | integer | Core Field (0) | csb_pl_drug_secondary_reason_respiratory | integer | Setup Standard Field (1) | ||
| 19 | Not Used (1) | condition | integer | Core Field (0) | csb_pl_drug_secondary_reason_digestive | integer | Setup Standard Field (1) | ||
| 20 | Not Used (1) | condition | integer | Core Field (0) | csb_pl_drug_secondary_reason_lameness | integer | Setup Standard Field (1) | ||
| 21 | Not Used (1) | condition | integer | Core Field (0) | csb_pl_drug_secondary_reason_neurologic | integer | Setup Standard Field (1) | ||
| 22 | Not Used (1) | condition | integer | Core Field (0) | csb_pl_drug_secondary_reason_mixed | integer | Setup Standard Field (1) | ||
| 23 | Not Used (1) | condition | varchar (128) | Core Field (0) | csb_pl_drug_secondary_reason_mixed_description | varchar (128) | Setup Standard Field (1) | ||