4 Replies Latest reply on Aug 10, 2018 10:07 AM by Jeff Stanley

# Filter out records

I have a data set that includes a combination of block accounts (master accounts with sub accounts) and individual accounts which includes the sub accounts. The block account numbers are identified as starting with "888," and then with a unique "name." within the set of "888's." In the individual accounts, the account numbers start with "111," these also have an account name that may or may not be unique. When creating a final list of accounts I need to filter out the "111" accounts that  share a name with an "888" account. In the sample attached in 10.3, the full list of account is:

So I would need to filter out the red and blue "111" accounts since any activity they have would already be included in the "888" accounts of the same name (ignore the fact the amounts don't indicate that I just used random numbers). "111" accounts can share a name, this just means that the same customer has multiple individual accounts that do not roll into one - so the two "111' accounts named "black" are fine.

• ###### 1. Re: Filter out records

Hi Jeff,

You can try below approach:

1. account = RIGHT(STR([Account number]),LEN(STR([Account number]))-3)

this will extract the account num  except the first 3 digits.

2. Flag :  { FIXED [account]: (IF (COUNT([Name])=COUNTD([Name])) then 'select' else 'reject'  END)}

• ###### 2. Re: Filter out records

that is a great start, but I only want to filter out the "111" records that share a name with a "888", the "888" should not be rejected. I also did not notice that the account numbers were the same on the 888 and 111 records, that is just a coincidence in making the random data - I really need to key off the "name" field to filter out.

• ###### 3. Re: Filter out records

HI Jeff,

If you want to have logic for only 111 and 888 then below calc you can try:

1. Left = left(str([Account number]),3)

2. chk = {FIXED [Name]: if COUNTD([left])<>1 then "No" else "yes" end}

3. Flag=  if [chk]="No" and [left]="111" then "reject" END

1 of 1 people found this helpful
• ###### 4. Re: Filter out records

this is great, thanks