I'm trying to pull together a list of accounts that do not have specific contact fields populated. What I created though is just returning the contacts that meet the criteria.
For example, I want a list of Accounts that do not have any Project Manager contacts assigned. Account + Project Manager = null returns the contacts that are not Project Managers, but it doesn't mean there aren't contacts that are a Project Manager. I'm not sure how to incorporate the additional/different qualifier that returns the contacts that aren't a Project Manager only for the Accounts for which there is no Project Manager.
Thoughts?
Solved
How do you pull accounts when no contacts meet the criteria, not the contacts that meet the criteria
+3Best answer by jitin_mehndiratta
Hi Kelly,
This is achievable using Pivot task in Bionic Rules along with Case formula in Transformation task.
Example:
You can apply case formula in Transformation task to your data so that "true" is replaced by 1 and "False" by 0. The Output dataset will be as follows:
Account A
Contact 1 - Project Manager = 1
Contact 2 - Project Manager = 0
Contact 3 - Project Manager = 0
Account B:
Contact 1 - Project Manager = 1
Contact 2 - Project Manager = 0
Contact 3 - Project Manager = 1
Account C:
Contact 1 - Project Manager = 0
Contact 2 - Project Manager = 0
Contact 3 - Project Manager = 0
Then use the Pivot task in Rules to get the output as follows(use Sum as an aggregation in Pivot) :
Accounts Project Managers
Account A 1
Account B 2
Account C 0
Apply
Filter condition : Project managers = 0
You will get Account C in the output as desired.
Let me know if there are any questions.
Regards,
Jitin
This is achievable using Pivot task in Bionic Rules along with Case formula in Transformation task.
Example:
You can apply case formula in Transformation task to your data so that "true" is replaced by 1 and "False" by 0. The Output dataset will be as follows:
Account A
Contact 1 - Project Manager = 1
Contact 2 - Project Manager = 0
Contact 3 - Project Manager = 0
Account B:
Contact 1 - Project Manager = 1
Contact 2 - Project Manager = 0
Contact 3 - Project Manager = 1
Account C:
Contact 1 - Project Manager = 0
Contact 2 - Project Manager = 0
Contact 3 - Project Manager = 0
Then use the Pivot task in Rules to get the output as follows(use Sum as an aggregation in Pivot) :
Accounts Project Managers
Account A 1
Account B 2
Account C 0
Apply
Filter condition : Project managers = 0
You will get Account C in the output as desired.
Let me know if there are any questions.
Regards,
Jitin
+1Hi Kelly,
This appears to be achievable via Merge task in Bionic rules. Can you elaborate some more about the problem statement. It will be helpful if you can give an example in a tabular form with some sample data and the expected output so that I am able to help you better and the best way of achieving this.
This appears to be achievable via Merge task in Bionic rules. Can you elaborate some more about the problem statement. It will be helpful if you can give an example in a tabular form with some sample data and the expected output so that I am able to help you better and the best way of achieving this.
+3Hi Sai:
Account A:
Contact 1 - Project Manager = True
Contact 2 - Project Manager = False
Contact 3 - Project Manager = False
Account B:
Contact 1 - Project Manager = True
Contact 2 - Project Manager = False
Contact 3 - Project Manager = True
Account C:
Contact 1 - Project Manager = False
Contact 2 - Project Manager = False
Contact 3 - Project Manager = False
Given the above scenario, I want a query that returns just Account C. Instead, the results that returned were Account A: Contact 1 and 2, Account B: Contact 2, and Account C: Contact 1, 2, and 3.
I was able to replicate a rule another user created, however any steps that could be listed should anyone else search for something similar I'm sure it would be helpful.
Account A:
Contact 1 - Project Manager = True
Contact 2 - Project Manager = False
Contact 3 - Project Manager = False
Account B:
Contact 1 - Project Manager = True
Contact 2 - Project Manager = False
Contact 3 - Project Manager = True
Account C:
Contact 1 - Project Manager = False
Contact 2 - Project Manager = False
Contact 3 - Project Manager = False
Given the above scenario, I want a query that returns just Account C. Instead, the results that returned were Account A: Contact 1 and 2, Account B: Contact 2, and Account C: Contact 1, 2, and 3.
I was able to replicate a rule another user created, however any steps that could be listed should anyone else search for something similar I'm sure it would be helpful.
+3Sai -this has come up again, any guidance here? To address previously I used the Rules Engine but this is a need for a query in Programs.
Hi Kelly,
This is achievable using Pivot task in Bionic Rules along with Case formula in Transformation task.
Example:
You can apply case formula in Transformation task to your data so that "true" is replaced by 1 and "False" by 0. The Output dataset will be as follows:
Account A
Contact 1 - Project Manager = 1
Contact 2 - Project Manager = 0
Contact 3 - Project Manager = 0
Account B:
Contact 1 - Project Manager = 1
Contact 2 - Project Manager = 0
Contact 3 - Project Manager = 1
Account C:
Contact 1 - Project Manager = 0
Contact 2 - Project Manager = 0
Contact 3 - Project Manager = 0
Then use the Pivot task in Rules to get the output as follows(use Sum as an aggregation in Pivot) :
Accounts Project Managers
Account A 1
Account B 2
Account C 0
Apply
Filter condition : Project managers = 0
You will get Account C in the output as desired.
Let me know if there are any questions.
Regards,
Jitin
This is achievable using Pivot task in Bionic Rules along with Case formula in Transformation task.
Example:
You can apply case formula in Transformation task to your data so that "true" is replaced by 1 and "False" by 0. The Output dataset will be as follows:
Account A
Contact 1 - Project Manager = 1
Contact 2 - Project Manager = 0
Contact 3 - Project Manager = 0
Account B:
Contact 1 - Project Manager = 1
Contact 2 - Project Manager = 0
Contact 3 - Project Manager = 1
Account C:
Contact 1 - Project Manager = 0
Contact 2 - Project Manager = 0
Contact 3 - Project Manager = 0
Then use the Pivot task in Rules to get the output as follows(use Sum as an aggregation in Pivot) :
Accounts Project Managers
Account A 1
Account B 2
Account C 0
Apply
Filter condition : Project managers = 0
You will get Account C in the output as desired.
Let me know if there are any questions.
Regards,
Jitin
+3Thank you Jitin, I'm going to give that a shot!
+1Hi Kelly,
Did the suggestion given by @Jitin work for you? Do you need any help here?
Always welcome for any queries.
Did the suggestion given by @Jitin work for you? Do you need any help here?
Always welcome for any queries.
Sign up
If you ever had a profile with us, there's no need to create another one.
Don't worry if your email address has since changed, or you can't remember your login, just let us know at community@gainsight.com and we'll help you get started from where you left.
Else, please continue with the registration below.
Welcome to the Gainsight Community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.