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

Actuarial Jobs by State

New York  New Jersey  Connecticut  Massachusetts 
California  Florida  Texas  Illinois  Colorado


Reply
 
Thread Tools Search this Thread Display Modes
  #21  
Old 02-11-2019, 10:44 PM
rgreenlee's Avatar
rgreenlee rgreenlee is offline
Member
Non-Actuary
 
Join Date: Jun 2014
Location: in a van down by the river
Posts: 1,071
Default

Quote:
Originally Posted by Cavaliere View Post
I'd first like to thank the people who helped me in my last post.

Here's my new issue:

I'm trying to search a bunch of accounts in column A, where there are plenty of duplicates, and find the ones where there are different values for column B.

Example:

Column A_____Column B
1____________a
1____________a
2____________c
2____________c
3____________d
3____________e
1____________a
1____________b


I would want to return number 1 and number 3 ( or have 1 and 3 be TRUE, and 2 be FALSE) because their second column values aren't all the same.

Thank you in advance!
If you load your dataset into SQL table called "MyDataSet" this becomes simple:

SELECT
A.AccountNumber,A.ColumnB as ColumnB1,ISNULL(B.ColumnB,'') as ColumnB2
FROM
MyDataSet AS A
LEFT OUTER JOIN
MyDataSet AS B
ON
A.AccountNumber=B.AccountNumber AND A.ColumnB>B.ColumnB
The output will be the recordset of all account numbers along with every combination of mismatched columns. You can use an inner join, but you may miss some records for which there is a unique value of ColumnB on the account.

You could also tweak this to serve your end purpose, but this concept is extremely useful. There are many times in which I use SQL to do stuff like this - although it doesn't fit well into processes you want to automate with one press of a button.

An Excel Idea:

You can place them into a pivot table and Let the account number be the row. Use ColumnB as an aggregate and select "Min" instead of "Count" (or sum). Add Column B again as an aggregate and select "Max" instead of "Count" (or sum).

You will have a pivot table consisting of:

AccountNumber,ColumnBMin,ColumnBMax

If Excel does not agree to you using the datatype of columnB in an aggregate, create a unique numeric identifier for each unique value in column B by numbering them from 1 to 100 (or whatever the number of unique ColumnB values). Then lookup to get that number next to your identifier and perform the same aggregate function in the pivot table on the unique integer.

You will then have a pivot table consisting of:

AccountNumber,ColumnBMinIndexNumber,ColumnBMaxInde xNumber

From there you can lookup the original columnB value for each min and max and you have the desired result.

The biggest drawback to the excel approach is when you have more than two unique columnB records with the same account number, you will be missing at least one from the finished product.

Last edited by rgreenlee; 02-12-2019 at 07:09 AM..
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
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:19 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, 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.12357 seconds with 9 queries