Mar
9th
Mon
9th
notes to self
- for severity, found negative correlation between location and month.
- PD losses seem like lit - not indep. of loss/dev
Jan
28th
Wed
28th
Excel: equivalent of VLOOKUP with multiple criteria
I found myself coming back to this example today.
Jan
26th
Mon
26th
I needed to calculate the difference between two date fields today.
cast( convert( datetime, datestring1, 101) - convert( datetime, datestring2, 101) as float )
I’m not sure what “101” does…
Jan
8th
Thu
8th
replacing newline characters in a spreadsheet
wanted to remove newline characters / carriage returns from inside of cells in a spreadsheet.
First, I needed to determine the character code of the newline character. I pressed F2 to enter cell edit mode and selected just the newline character, copying and pasting the character into a new cell. I used CODE() to determine the character code - it was 10.
I could then use SUBSTITUTE(<source cell>, CHAR(10), “”) to remove the newline character.
Jan
7th
Wed
7th
SQL Server Management Studio - exporting fields containing commas
I found Aaron Bertrand’s comment on February 23, 2007 at 7:56 AM on his own post tremendously useful for exporting data from SQL Server Management Studio to Excel.
Under “Tools | Options | Query Results | SQL Server | Results to Grid”, I recommend checking “Quote strings containing list separators when saving .csv results”. Without this option selected, fields containing commas separate into two fields when exported to Excel. This option seems to have come available with SP2, so if you do not see the option, you might want to inquire into that. While you’re in there, you may also want to check the boxes for including the query in the results and including column headers when exporting the results (I did).
The comment’s second tip about selecting “Save with encoding” from the drop-down next to the Save button and saving as ANSI rather than Unicode also proved useful for correctly opening Excel files through Windows Explorer.
my intentions for this blog
i intend to use this blog to record notes about things i learn at work that might be useful to others doing similar things. i expect topics will center around accessing data (from SQL Server, Access, etc.), manipulating data (with SQL, Excel, R, etc.), analyzing data / Econometrics (with SAS, R, Excel, etc.), and sharing findings (with Excel, PowerPoint, R, etc.).
note: I do not prefer Microsoft data tools - the data I use happens to be stored with SQL Server and Access.
