Hi,
I have an app that is doing a binary load of our data model which has a lot of data. I use this app to bring in data from excels and other data sources. I wanted to see if there is a simple loading script solution for replacing values in a field. For example, in my data model I have a table called “Base Table” that has about 100 column which is unique on the field “ItemNo”. I have five fields in this table that are called “Value_1”, “Value_2”, “Value_3”, “Value_4” & “Value_5”. I have about 90k distinct values for “ItemNo”. I have an excel file that I am loading in following my binary load that contains data for 1,000 of the “ItemNos” that are in the data model. In this excel, I have data for the 5 “Value” fields that I feel are more accurate than what is in my data model. So I see two possible options. I could load in these 5 fields from my excel with a different naming convention (for example, “Value_1_External”, “Value_2_External”…) or I could somehow replace the data for these 5 fields for just these 1,000 “ItemNos”.
I really would rather do the second option. I wrote a script that does the following steps:
- Load in excel file into table “New Data”
- Create a Mapping table for just the “ItemNos” that exist in the excel file.
- Create a new table called “Temp” which loads in all of the data from my “Base Table” for ItemNos that are in the excel. I use the Mapping table to isolate to my population that is only in the excel.
- Drop the fields “Value_1”, “Value_2”, “Value_3”, “Value_4” & “Value_5” from “Temp”
- Left Join data from “New Data” to “Temp”. This will bring in the correct data for fields “Value_1”, “Value_2”, “Value_3”, “Value_4” & “Value_5”
- Concatenate to “Temp” table all data from “Base Table” for only ItemNos that aren’t in the excel. Again I use the Mapping table to isolate to my population that is only in the excel.
- Drop “Base Table”. Rename “Temp” to “Base Table”
Please let me know if there is a process that is easier than this.
Thank you,
Brian
