Production Lot Upload Data Preparation: Coaldale Veterinary Clinic (CVC)
Site Information Details
Coaldale Veterinary Clinic (3) (CVC)Vet: CSBV00004 (4)
Project Year: 2021
The site has four (4) herds. They are:
CSBH0000030
CSBH0000031
CSBH0000032
CSBH0000033
CVC 2019
CVC 2020
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.
A new column is to be added to the source spreadsheet. The column name is called "change_to_rows".
As the name implies, insert the column where the core data as columns of information, changes to drug information
which are rows of data. Each value in the "change_to_rows" column is "<<row>>". When it is setup in this way
the program to upload the data knows where column data changes to row based data for the drugs.
The rows of drug data represent 1 drug, usually a product name and active ingredient for 1 drug route.
The product name in the target database is called a trade name. The combination of both trade name and active ingredient
are used in combination to acquire the correct trade name id and active ingredient id. What is new for 2020 is
each row in the source spreadsheet with a product name and active ingredient needs to be checked.
What is done is to make certain the trade name has 1 and only 1 active ingredient associated with it. If not,
the the upload takes into account and uploads the multiple active ingredients associated with the specific trade name.
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 or "closeout" 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
Make sure to format all date columns as "date" in the spreadsheet. When uploading into the database the data table
will take on the date format as expected. If you do not do this it appears it is less reliable to pickup we have a date.
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.
For the 2020 CVC data all feeds are the same. Only a few new columns were added to complete the data for upload.
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 (drug fields).
Here are the typical template settings used for CVC.
Name: CVC 2021 - Core All Herds
Level: Top Level (1)
Type: Core (1)
Route: Multiple Drugs (4)
Version: Version 2 (2)
By Row or Column: By Column (2)
Method: Populate template fields from Target (1)
|
||||||
|---|---|---|---|---|---|---|
| column_number | target_column_classify_field_setup | target_field_category | target_column_type | target_column_name | source_column_name | column_not_used |
| 1 | Setup Standard Field (1) | Core Field (0) | bigint | csb_survey_production_lot_id | csb_survey_production_lot_id | Not Used (1) |
| 2 | Setup Standard Field (1) | Core Field (0) | bigint | csb_survey_main_id | csb_survey_main_id | Not Used (1) |
| 3 | Setup Standard Field (1) | Core Field (0) | bigint | csb_record_group_id | csb_record_group_id | Not Used (1) |
| 4 | Setup Standard Field (1) | Core Field (0) | bigint | csb_account_id | csb_account_id | Not Used (1) |
| 5 | Setup Standard Field (1) | Core Field (0) | bigint | csb_survey_production_lot_upload_id | csb_survey_production_lot_upload_id | Not Used (1) |
| 6 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_template_id | csb_pl_template_id | Not Used (1) |
| 7 | Setup Standard Field (1) | Core Field (0) | varchar(128) | csb_pl_production_lot_code | feedlot_lot_name | Used (0) |
| 8 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_sex_id | sex | Used (0) |
| 9 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_animal_age_category_id | cattle_type | Used (0) |
| 10 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_percent_calves | csb_pl_percent_calves | Not Used (1) |
| 11 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_percent_yearlings | csb_pl_percent_yearlings | Not Used (1) |
| 12 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_origin_id | origin | Used (0) |
| 13 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_brd_risk_id | brd_risk | Used (0) |
| 14 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_n_animals_in | hd_in | Used (0) |
| 15 | Setup Standard Field (1) | Core Field (0) | date | csb_pl_avg_date_in | avg_date_in | Used (0) |
| 16 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_source_weight_in_units_id | csb_pl_source_weight_in_units_id | Not Used (1) |
| 17 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_source_weight_in_value | csb_pl_source_weight_in_value | Not Used (1) |
| 18 | Setup Multiple Target Fields (3) | Core Field (0) | numeric | csb_pl_avg_weight_in_kgs | avg_wt_in | Used (0) |
| 19 | Setup Standard Field (1) | Core Field (0) | date | csb_pl_avg_date_out | avg_date_out | Used (0) |
| 20 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_quarter_of_year_closeout_id | closeout | Used (0) |
| 21 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_source_weight_out_units_id | csb_pl_source_weight_out_units_id | Not Used (1) |
| 22 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_source_weight_out_value | csb_pl_source_weight_out_value | Not Used (1) |
| 23 | Setup Multiple Target Fields (3) | Core Field (0) | numeric | csb_pl_avg_weight_out_kgs | avg_wt_out | Used (0) |
| 24 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_n_animals_dead | deaths | Used (0) |
| 25 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_n_animals_slaughtered | hd_out | Used (0) |
| 26 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_total_days_on_feed | dof | Used (0) |
| 27 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_total_head_days | hddays | Used (0) |
Name: CVC 2021 - Drugs All Herds
Level: Second Level (2)
Type: Drug (2)
Route: Injectable (3)
Version: Version 1 (1)
By Row or Column: By Row (1)
Method: Populate template fields from Target (1)
|
||||||
|---|---|---|---|---|---|---|
| column_number | target_column_classify_field_setup | target_field_category | target_column_type | target_column_name | source_column_name | column_not_used |
| 1 | Setup Standard Field (1) | Core Field (0) | bigint | csb_survey_production_lot_drug_id | csb_survey_production_lot_drug_id | Not Used (1) |
| 2 | Setup Standard Field (1) | Core Field (0) | bigint | csb_survey_production_lot_id | csb_survey_production_lot_id | Not Used (1) |
| 3 | Setup Standard Field (1) | Core Field (0) | bigint | csb_survey_main_id | csb_survey_main_id | Not Used (1) |
| 4 | Setup Standard Field (1) | Core Field (0) | bigint | csb_record_group_id | csb_record_group_id | Not Used (1) |
| 5 | Setup Standard Field (1) | Core Field (0) | bigint | csb_account_id | csb_account_id | Not Used (1) |
| 6 | Setup Standard Field (1) | Core Field (0) | bigint | csb_survey_production_lot_upload_id | csb_survey_production_lot_upload_id | Not Used (1) |
| 7 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_template_id | csb_pl_template_id | Not Used (1) |
| 8 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_ordinal | csb_pl_drug_ordinal | Not Used (1) |
| 9 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_drug_route_id | route | Used (0) |
| 10 | Setup Standard Field (1) | Core Field (0) | varchar(192) | csb_pl_source_drug_name | active_ingredient | Not Used (1) |
| 11 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_source_drug_value | final_active_ingredient_amount | Not Used (1) |
| 12 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_source_drug_value_units_id | unit | Used (0) |
| 13 | Setup Standard Field (1) | Trade Name (2) | integer | csb_pl_drug_trade_name_id (TnId=) | product | Used (0) |
| 14 | Setup Standard Field (1) | Active Ingredient (1) | integer | csb_pl_drug_active_ingredient_id (AiId=) | active_ingredient | Used (0) |
| 15 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_drug_total_mg | final_active_ingredient_amount | Used (0) |
| 16 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_drug_total_ml | csb_pl_drug_total_ml | Not Used (1) |
| 17 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_drug_primary_reason_for_use | reason_for_use | Used (0) |
| 18 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_respiratory | condition | Used (0) |
| 19 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_digestive | condition | Not Used (1) |
| 20 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_lameness | condition | Not Used (1) |
| 21 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_neurologic | condition | Not Used (1) |
| 22 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_mixed | condition | Not Used (1) |
| 23 | Setup Standard Field (1) | Core Field (0) | varchar(128) | csb_pl_drug_secondary_reason_mixed_description | condition | Not Used (1) |