Production Lot Upload Data Preparation: Veterinary Agri-Health Services (VAHS)
Data Source Information
Veterinary Agri-Health Services (2) (VAHS)Vet: CSBV00002 (2)
Project Year: 2023
For 2023 data, the site has three (3) herds. They are:
CSBH0000024
CSBH0000026
CSBH0000054
Access to previous years documentation click on the appropriate link below:
VAHS 2019
VAHS 2020
VAHS 2021
VAHS 2022
There is 1 spreadsheet of source data provided by the site.
Within the spreadsheet/workbook of source data are 4 worksheets.
Each worksheet contains the data to upload.
Each worksheet represents 1 year of data for 1 herd.
The original VAHS 2020 data has the same or very similar data source structure to ABHS. ABHS was the first dataset to be uploaded.
All core production lot data, for one year, for one herd is at the beginning of the data.
All drug data related to the core data is after core data located at the end of the worksheet in columns.
All drug for all drug routes are available in the worksheet for the herd.
For the 2020 VAHS data we found other data which is more to our liking for uploading.
We deal with each herd's data separately. We took the core data and left it as is.
What was different we took the drug data and created a summary and turned column based drug data into rows!
The row drug data had 2 sections to it: treatment rows of data and prevention data.
Separate worksheets have been created for each herd.
All core data has the same core column names. All drug data has the same drug column names.
Because of these choices, one template was created for the core data, one for feed data and one for injectable data.
VAHS 2020 source data has primary and secondary reasons for use data in the drug worksheets.
There are many notes within each spreadsheet for each herd. Please look for a worksheet tab called "Notes" for more information.
Below are a few suggestions when we worked with 2019 VAHS data. It will be similar for 2020 VAHS data.
All herds do not have columns of data to match percent calves and percent yearlings in the target database.
There is a column for herds 23, 24 and 26 called 'Animal Type' which can be coded to the values we want.
A similar column exists for herd 25 and is called 'Calf or yearling'.
A transformation or interpretation is made for these values into % calves or yearlings.
Here are how the interpretations for column 'Animal Type' for herds 23, 24 and 26:
WHC -> Calves - 100%
FSC -> Calves - 100%
FHC -> Calves - 100%
YS -> Yearlings - 100%
YH -> Yearlings - 100%
Here are how the interpretations for column 'Calf or yearling' for herd 25:
Calf -> Calves - 100%
Yearling -> Yearlings - 100%
Cow -> Adult - 100%
The sex field column name and values for Herd 25 are confounded with animal type. The column name is:
Sex (FPC, WPC, YL, etc.)
The actual sex categories are the following:
Steers
Heifers
Bulls
Cows
Mix
We add a new column for sex called:
sex final
We convert the values in the source column in the following way: source translation into sex code
WHC -> Heifers
FSC -> Steers
FHC -> Heifers
YS -> Steers
YH -> Heifers
COW -> Cows
Please note, there are limits to how long a source column field name can be for uploading.
I suggest we inform the source sites to keep the column names limited to less than 55 characters.
Production Lot Upload Data Preparation: Veterinary Agri-Health Services (VAHS)
Template Setup Suggestions
A one template setup is used for one or more years for each herd of data.Four templates are created, as mentioned above, one template for each herd. The primary, top level template created represents a mix of Core & Drug fields.
No secondary, second level child template is created for Drug fields.
When all data for both core and drugs are in columns, then most often, the template is populated from source.
*** Remember to setup the template so the source field "cipars" to the target field "csb_survey_main_id" field and set to not used!
*** Remember to setup the template so the source fields pertaining to weights as multi-field and make sure to add the fields for the target fields
*** csb_pl_avg_weight_in_kgs and csb_pl_avg_weight_out_kgs. Set associated target fields for source units and source value
*** for the weight in and weight out to be skipped.
Here are the typical template settings used for VAHS.
Name: VAHS 2023 - Core All Herds
Herd: All Herds
Level: Top Level (1)
Type: Core (1)
Route: Multple Drugs (99)
Version: Version 1 (1)
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 | cipars_feedlot_code *** | 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 | clinic_production_lot_code | Used (0) |
| 8 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_sex_id | sex | Used (0) |
| 9 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_sex_percent_steers | csb_pl_sex_percent_steers | Not Used (1) |
| 10 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_sex_percent_cows | csb_pl_sex_percent_cows | Not Used (1) |
| 11 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_sex_percent_heifers | csb_pl_sex_percent_heifers | Not Used (1) |
| 12 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_sex_percent_bulls | csb_pl_sex_percent_bulls | Not Used (1) |
| 13 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_animal_age_category_id | animal_type | Used (0) |
| 14 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_percent_calves | csb_pl_percent_calves | Not Used (1) |
| 15 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_percent_yearlings | csb_pl_percent_yearlings | Not Used (1) |
| 16 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_percent_adults | csb_pl_percent_adults | Not Used (1) |
| 17 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_origin_id | origin_auction_ranch_direct_or_backgro | Used (0) |
| 18 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_origin_percent_auction | csb_pl_origin_percent_auction | Not Used (1) |
| 19 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_origin_percent_backgrounded | csb_pl_origin_percent_backgrounded | Not Used (1) |
| 20 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_origin_percent_ranch_direct | csb_pl_origin_percent_ranch_direct | Not Used (1) |
| 21 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_brd_risk_id | brd_risk | Used (0) |
| 22 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_brd_risk_percent_low | csb_pl_brd_risk_percent_low | Not Used (1) |
| 23 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_brd_risk_percent_medium | csb_pl_brd_risk_percent_medium | Not Used (1) |
| 24 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_brd_risk_percent_high | csb_pl_brd_risk_percent_high | Not Used (1) |
| 25 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_quarter_of_year_closeout_id | qtr_close_out | Used (0) |
| 26 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_n_animals_in | hd_in | Used (0) |
| 27 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_n_animals_railed | railed | Used (0) |
| 28 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_n_animals_dead | hd_died | Used (0) |
| 29 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_n_animals_slaughtered | hd_shipped | Used (0) |
| 30 | 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) |
| 31 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_source_weight_in_value | csb_pl_source_weight_in_value | Not Used (1) |
| 32 | Setup Multiple Target Fields (3) | Core Field (0) | numeric | csb_pl_avg_weight_in_kgs | avg_arrival_weight_kg | Used (0) |
| 33 | 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) |
| 34 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_source_weight_out_value | csb_pl_source_weight_out_value | Not Used (1) |
| 35 | Setup Multiple Target Fields (3) | Core Field (0) | numeric | csb_pl_avg_weight_out_kgs | ship_wt_kg | Used (0) |
| 36 | Setup Standard Field (1) | Core Field (0) | date | csb_pl_avg_date_in | avg_arrival_date | Used (0) |
| 37 | Setup Standard Field (1) | Core Field (0) | date | csb_pl_avg_date_out | ship_date | Used (0) |
| 38 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_total_days_on_feed | dof | Used (0) |
| 39 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_total_head_days | average_head_days | Used (0) |
| 40 | Setup Standard Field (1) | Core Field (0) | varchar | csb_pl_notes | csb_pl_notes | Not Used (1) |
*** Remember to setup the template so the source drug field "clinic_production_lot_code" to the target field "csb_survey_production_lot_drug_id" field and set to not used!
*** Remember to do this for each drug route of data provided.
Here are the typical template settings used for VAHS.
Name: VAHS 2023 - Drug Feed
Herd: All Herds
Level: Second Level (2)
Type: Drug (2)
Route: Feed (2)
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 | clinic_production_lot_code *** | 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 | Used (0) |
| 11 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_source_drug_value | total_mg_of_active_ingredient | Used (0) |
| 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 | active_ingredient | Used (0) |
| 14 | Setup Standard Field (1) | Active Ingredient (1) | integer | csb_pl_drug_active_ingredient_id | active_ingredient | Used (0) |
| 15 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_drug_total_mg | total_mg_of_active_ingredient | 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 Standard Field (1) | Core Field (0) | bigint | csb_pl_drug_regimen_id | csb_pl_drug_regimen_id | Not Used (1) |
| 18 | Setup Standard Field (1) | Core Field (0) | bigint | csb_pl_drug_regimen_detail_id | csb_pl_drug_regimen_detail_id | Not Used (1) |
| 19 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_drug_primary_reason_for_use | reason_for_use_preventioncontrol_or_treatment | Used (0) |
| 20 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_drug_primary_reason_for_use_pct_gp | csb_pl_drug_primary_reason_for_use_pct_gp | Not Used (1) |
| 21 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_drug_primary_reason_for_use_pct_dp | csb_pl_drug_primary_reason_for_use_pct_dp | Not Used (1) |
| 22 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_drug_primary_reason_for_use_pct_dt | csb_pl_drug_primary_reason_for_use_pct_dt | Not Used (1) |
| 23 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_respiratory | condition_for_use | Used (0) |
| 24 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_digestive | csb_pl_drug_secondary_reason_digestive | Not Used (1) |
| 25 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_lameness | csb_pl_drug_secondary_reason_lameness | Not Used (1) |
| 26 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_neurologic | csb_pl_drug_secondary_reason_neurologic | Not Used (1) |
| 27 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_liver_abscess | csb_pl_drug_secondary_reason_liver_abscess | Not Used (1) |
| 28 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_coccidiosis | csb_pl_drug_secondary_reason_coccidiosis | Not Used (1) |
| 29 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_enteritis | csb_pl_drug_secondary_reason_enteritis | Not Used (1) |
| 30 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_mixed | csb_pl_drug_secondary_reason_mixed | Not Used (1) |
| 31 | Setup Standard Field (1) | Core Field (0) | varchar(128) | csb_pl_drug_secondary_reason_mixed_description | csb_pl_drug_secondary_reason_mixed_description | Not Used (1) |
| 32 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_other | csb_pl_drug_secondary_reason_other | Not Used (1) |
| 33 | Setup Standard Field (1) | Core Field (0) | varchar(128) | csb_pl_drug_secondary_reason_other_description | csb_pl_drug_secondary_reason_other_description | Not Used (1) |
| 34 | Setup Standard Field (1) | Core Field (0) | varchar | csb_pl_drug_secondary_reason_source | csb_pl_drug_secondary_reason_source | Not Used (1) |
| 35 | Setup Standard Field (1) | Core Field (0) | varchar | csb_pl_drug_notes | csb_pl_drug_notes | Not Used (1) |
Here are the typical template settings used for VAHS.
Name: VAHS 2023 - Drug Injectable
Herd: All Herds
Level: Top Level (1)
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 | clinic_production_lot_code *** | 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_of_administration_general | Used (0) |
| 10 | Setup Standard Field (1) | Core Field (0) | varchar(192) | csb_pl_source_drug_name | active_ingredient_original | Used (0) |
| 11 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_source_drug_value | total_mg_of_active_ingredient | Used (0) |
| 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 | csb_pl_drug_trade_name_id | Not Used (1) |
| 14 | Setup Standard Field (1) | Active Ingredient (1) | integer | csb_pl_drug_active_ingredient_id | active_ingredient_original | Used (0) |
| 15 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_drug_total_mg | total_mg_of_active_ingredient | 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 Standard Field (1) | Core Field (0) | bigint | csb_pl_drug_regimen_id | csb_pl_drug_regimen_id | Not Used (1) |
| 18 | Setup Standard Field (1) | Core Field (0) | bigint | csb_pl_drug_regimen_detail_id | csb_pl_drug_regimen_detail_id | Not Used (1) |
| 19 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_drug_primary_reason_for_use | reason_for_use_preventioncontrol_or_treatment | Used (0) |
| 20 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_drug_primary_reason_for_use_pct_gp | csb_pl_drug_primary_reason_for_use_pct_gp | Not Used (1) |
| 21 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_drug_primary_reason_for_use_pct_dp | csb_pl_drug_primary_reason_for_use_pct_dp | Not Used (1) |
| 22 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_drug_primary_reason_for_use_pct_dt | csb_pl_drug_primary_reason_for_use_pct_dt | Not Used (1) |
| 23 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_respiratory | condition_for_use | Used (0) |
| 24 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_digestive | csb_pl_drug_secondary_reason_digestive | Not Used (1) |
| 25 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_lameness | csb_pl_drug_secondary_reason_lameness | Not Used (1) |
| 26 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_neurologic | csb_pl_drug_secondary_reason_neurologic | Not Used (1) |
| 27 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_liver_abscess | csb_pl_drug_secondary_reason_liver_abscess | Not Used (1) |
| 28 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_coccidiosis | csb_pl_drug_secondary_reason_coccidiosis | Not Used (1) |
| 29 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_enteritis | csb_pl_drug_secondary_reason_enteritis | Not Used (1) |
| 30 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_mixed | csb_pl_drug_secondary_reason_mixed | Not Used (1) |
| 31 | Setup Standard Field (1) | Core Field (0) | varchar(128) | csb_pl_drug_secondary_reason_mixed_description | csb_pl_drug_secondary_reason_mixed_description | Not Used (1) |
| 32 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_other | csb_pl_drug_secondary_reason_other | Not Used (1) |
| 33 | Setup Standard Field (1) | Core Field (0) | varchar(128) | csb_pl_drug_secondary_reason_other_description | csb_pl_drug_secondary_reason_other_description | Not Used (1) |
| 34 | Setup Standard Field (1) | Core Field (0) | varchar | csb_pl_drug_secondary_reason_source | csb_pl_drug_secondary_reason_source | Not Used (1) |
| 35 | Setup Standard Field (1) | Core Field (0) | varchar | csb_pl_drug_notes | csb_pl_drug_notes | Not Used (1) |