Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Identifying Accounts to prioritize base on other fields

Mwatts23
8 - Asteroid

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 HK1Nike HQ

10

Child with Activity 1
Nike HK1Nike HQ

10

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

10

Child with Activity 1
Nike FR3Nike HQ

10

Child without Opportunity  
Nike UK4Nike HQ10Child with Activity 1
Nike UK4Nike HQ10Child with Opportunity1 
Nike HQ10Nike HQ10Parent with Activity 1
Nike 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

 

 

 

6 REPLIES 6
RolandSchubert
16 - Nebula
16 - Nebula

Hi @Mwatts23 ,

 

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

 

2022-01-28_15-50-39.jpg

 

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
15 - Aurora
15 - Aurora

@Mwatts23,

 

Please see below :

 

messi007_0-1643382099658.png

 

Attached the workflow,

Regards,

Mwatts23
8 - Asteroid

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
8 - Asteroid

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
16 - Nebula
16 - Nebula

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
8 - Asteroid

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

Labels