Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

DW Simpson International Actuarial Jobs
Canada  Asia  Australia  Bermuda  Latin America  Europe


Reply
 
Thread Tools Display Modes
  #1  
Old 10-12-2017, 12:10 PM
paulbernal paulbernal is offline
Member
 
Join Date: Oct 2009
Posts: 236
Default Duplicate records generated when exporting R forecasts to a SQL Server Table

Dear friends,

Hope you are all doing great. I developed a forecasting model in R from within Microsoft Azure Machine Learning Studio and every time I run the model, forecast results are exported to a SQL Server table.

The problem is, whenever i run the model records get appended with the same records over and over again, thus creating duplicates.

I would like to know if I could prevent that from happening either from SQL Server or from R itself.

Any help will be greatly appreciated,

Best regards,

Paul
Reply With Quote
  #2  
Old 10-12-2017, 02:07 PM
Colonel Smoothie's Avatar
Colonel Smoothie Colonel Smoothie is offline
Member
CAS
 
Join Date: Sep 2010
College: Jamba Juice University
Favorite beer: AO Amber Ale
Posts: 45,200
Default

Quote:
Originally Posted by paulbernal View Post
Dear friends,

Hope you are all doing great. I developed a forecasting model in R from within Microsoft Azure Machine Learning Studio and every time I run the model, forecast results are exported to a SQL Server table.

The problem is, whenever i run the model records get appended with the same records over and over again, thus creating duplicates.

I would like to know if I could prevent that from happening either from SQL Server or from R itself.

Any help will be greatly appreciated,

Best regards,

Paul
Do you want to retain the forecasts for each model run, or do you want to just have the output from the most recent run in that table?
__________________
Recommended Readings for the EL Actuary || Recommended Readings for the EB Actuary

Quote:
Originally Posted by Wigmeister General View Post
Don't you even think about sending me your resume. I'll turn it into an origami boulder and return it to you.
Reply With Quote
  #3  
Old 10-12-2017, 03:32 PM
paulbernal paulbernal is offline
Member
 
Join Date: Oct 2009
Posts: 236
Default

Quote:
Originally Posted by Colonel Smoothie View Post
Do you want to retain the forecasts for each model run, or do you want to just have the output from the most recent run in that table?
Dear friend, what I want to do is only append to the table new dates (that is, information that was not on the table before).

If I already generated forecasts for periods october-2017 to september-2017, if I rerun the forecasts to the same period, Id want those to replace the old ones, and, if there are new months then append the new ones but not append the same records over and over again.

Thank you in advance!
Reply With Quote
  #4  
Old 10-16-2017, 05:41 PM
AMedActuary AMedActuary is offline
Member
SOA
 
Join Date: May 2007
College: UCLA Alumni
Posts: 369
Default

Hi Paul,

What package are you using to upload the data to SQL server ('odbc')?

I wonder if the 'odbc' package has a way to only update the new dates. I haven't explored the documentation enough to tell. The other option would be to download the table from the database and do an anti-join on the forecast date with your new table to filter out the ones that already exist. Then you can upload the filtered table only.
Reply With Quote
  #5  
Old 10-18-2017, 03:33 AM
d45's Avatar
d45 d45 is offline
Member
SOA
 
Join Date: Sep 2010
Location: Europe
Studying for Life
Favorite beer: Espresso
Posts: 181
Default

Add a few lines of code to delete the previous records from the SQL server table (if exist) then update the table with the new records.
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 10:00 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.18298 seconds with 9 queries