Identifying Accounts to prioritize base on other fields

[Mwatts23]

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 Identifier	Opportunity  Count	Activity Count
Nike HK	1	Nike HQ	10	Child with Activity		1
Nike HK	1	Nike HQ	10	Child without Opportunity
Nike SG	2	Nike HQ	10	Child without Activity
Nike SG	2	Nike HQ	10	Child with Opportunity	1
Nike FR	3	Nike HQ	10	Child with Activity		1
Nike FR	3	Nike HQ	10	Child without Opportunity
Nike UK	4	Nike HQ	10	Child with Activity		1
Nike UK	4	Nike HQ	10	Child with Opportunity	1
Nike HQ	10	Nike HQ	10	Parent with Activity		1
Nike HQ	10	Nike HQ	10	Parent with Opportunity	1

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 solution]

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 solution]

@Mwatts23,

Please see below :

Attached the workflow,

Regards,

[Mwatts23 · Accepted solution]

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

[Mwatts23]

Thank you! @messi007 This is a great workflow as well. Is there a way I can put back the 3 missing rows from the original data in the Union tool and Tag them as "Priority" too?

Best Regards,

Mike 

[RolandSchubert · Accepted solution]

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

[Mwatts23]

Thank you @RolandSchubert  for the explanation. All clear now.