Identifying Accounts to prioritize base on other fields

Question

Hi,

I have this sample data source below.

Account

Name

Account

Name ID

Parent

Account

Parent

Account ID

With and Without Activity or Opportunity Parent/Child IdentifierOpportunity

Count

Activity CountNike HK1Nike HQ10

Child with Activity 1Nike HK1Nike HQ10

Child without Opportunity  Nike SG2Nike HQ10Child without Activity  Nike SG2Nike HQ10Child with Opportunity1 Nike FR3Nike HQ10

Child with Activity 1Nike FR3Nike HQ10

Child without Opportunity  Nike UK4Nike HQ10Child with Activity 1Nike UK4Nike HQ10Child with Opportunity1 Nike HQ10Nike HQ10Parent with Activity 1Nike HQ10Nike HQ10Parent with Opportunity1

What I would like to do is to tag Account Names Nike UK & Nike HQ as "Not Priority Accounts" since they have an

Opportunity and Activity value. The Nike HK, SG & FR will be tag as "Priority Accounts" since they only have either an Opportunity or Activity value.

I tried the formula below but there are accounts that were tag as "Not Priority Accounts" even though they don't have both an Opportunity and Activity value.

if [With and Without Activity or Opportunity Parent/Child Identifier] in("Child Without Activity","Parent Without Activity","Child Without Opportunity","Parent Without Opportunity") then "Priority Accounts" else "Not Priority Accounts" endif

I hope you can help me on creating the correct formula. Please let me know if you need additional details.

Many Thanks!

Mike

RolandSchubert · Accepted Answer

Hi @Mwatts23 ,

my approach would be to check each pair of rows using a Multi-Row Formula tool:

The first row always is NULL and will be filtered out, the second row has the flag you need.

Let me know if it works for you.

Best,

Roland

messi007 · Accepted Answer

@Mwatts23,

Please see below :

Attached the workflow,

Regards,

Mwatts23 · Accepted Answer

It works perfectly! @RolandSchubert Thank you so much!.

I hope you can help me understand,

1. what part of the workflow changes the Flag "not priority account" to "priority account"? for Account name "Nike FR & HK"

2. Can this process works as well if there are 3 or more rows of the same account name?

Many Thanks!

Mike

RolandSchubert · Accepted Answer

Hi @Mwatts23 ,

in the Multi-Row Formula tool, I check if one of the rows (the current row and the row before) contain "without" - if it is found, flag is set to "not priority account", else it is "priority". The Filter tool removes the first row of each group and the Find Replace tool assigns the flag to the account names.

If there are more rows, you can extend the logic within the Multi-Row Formula tool, but it depends on the rules. If the general rule is, that "if one row is without activity, set account to priority", and there is a variable number of rows for each account, I would concatenate all activity rows for an account and decide on the concatenated value.

I added a sample workflow using the approach I mentioned for multiple rows.

Best,

Roland