[SalesForce] Issue processing data from aggregate result query

I have a master-detail relation ship where I am updating the parent (lets call it Parent__c) based on its child records aggregated values
(lets call the child, Child__c).
The child has a relationship to another custom obj, lets call this one LookupObj__c, in it a picklist, lets call it Category__c.

I need a query that will determine the 2 most frequent LookupObj__c picklist values based on the relationship with Child__c. For this I came up with an
aggregate query that groups on Parent, and on Category then order on the count desc so that I get the highest 2 per parent first on the results
and where there is even results (lets say a match on category counts, then it is determined on category text comparison (so A_category would come before B_category)

So far I have something decent built where I get the results order the way I want BUT I am unsure how to get ONLY the first 2 most frequent LookupObj__c values
for each parent and set that on the parent

Code:

for(AggregateResult ar: [SELECT count(id) cnt, LookupObj__r.Category__c cat, Parent__c Parent,
          Parent__r.Primary_Category__c,Parent__r.Secondary_Category__c 
         FROM Child__c
         WHERE Parent__c IN :ParentsToUpdateSet
         GROUP BY Parent__c, LookupObj__r.Category__c ,
             Parent__r.Primary_Category__c,Parent__r.Secondary_Category__c 
         order by Parent__c, count(id) desc, LookupObj__r.Category__c asc
        ]){
   system.debug('aggregateResult: ' + ar);
   Parent__c Parent = new Parent__c();
   Parent.Id = (id)ar.get('Parent');
   String cat = (String)ar.get('cat');
   //HOW DO I GET THE 2 MOST FREQUENT CATEGORIES PER TRIP ASSIGNED???
   parent.primary_category__c = 
   parent.seconday_Category__c = 

  }

Best Answer

There are several ways of doing this.

One would be to sort your GroupBy results using a for loop.

Another would be to put the results into map(s). Then take the top two results from your sorting in order to get them for the respective parents.

Since you're using a GroupBy, you could also presumably return something "like" ar[0].cnt, ar[0].Parent__r.Primary_Category__c and ar[1].cnt, ar[1].Parent__r.Primary_Category__c to get the top two for the parent with the "most" results and compare the two Parent_r's to see if they're the same. If they're not, you have the two that you're looking for that are at the top.

If they are the same, you'll need to test ar[3], ar[4], etc until you find a different Parent__r to assign as the second parent.

From the above, you'll already know the results for the highest Primary_Category__c for the first Parent__r and for the 2nd one as well. Now you need to sort the secondary category based on the what you learned determined to be the 1st and second parents.

I believe this is where you may want to put the results into two maps.

Beginning with the 1st parent, do a for loop to add the results of a loop to the map if the parent and the secondary_category__c matches parent__r that was the 1st parent and secondary_category__c isEmpty() == false. The first result you find will be the one you're looking for. Rather than putting it into a map, you could break from the for loop if you wanted to. the choice would be yours to make.

You'd repeat the same process above for the other parent__r.

Related Solutions

[SalesForce] Split a single field into 2 columns for a VF page

This is untested so you may need to have play, however this should return a List of the RevenueWrapper classes which contains the 4 columns you are looking to use in your VF page:

public class RevenueController{

Map<String, RevenueWrapper> ResultList = new Map<String, RevenueWrapper>(); 

public RevenueController(){
    List<AggregateResult> result =  [
    SELECT FISCAL_YEAR(Date__c) Date, Type__c, sum(Amount__c) Amount
    FROM Revenue__c
    GROUP BY FISCAL_YEAR(Date__c), Type__c];

    for(AggregateResult ar : result){

        RevenueWrapper RW = new RevenueWrapper();

        //If the RevenueWrapper already exists grab it from the Map
        if(ResultList.containsKey(String.valueOf(ar.get('Date')))){
            RW = ResultList.get(String.valueOf(ar.get('Date')));
        }

        //Set the amount
        RW.setAmount(ar);

        //Save back to the Map
        ResultList.put(String.valueOf(ar.get('Date')), RW);
    }
}

public List<RevenueWrapper> getResults() {
    return ResultList.values();
}


public class RevenueWrapper{
    public String revenueDate { get; set; }
    public Integer budgetAmount { get; set; }
    public Integer actualAmount { get; set; }
    public Integer varianceFYPercent { set; }
    public Integer varianceFYAmount { set; }

    public RevenueWrapper(){

    }

    public void setAmount(AggregateResult ar){
        revenueDate = String.valueOf(ar.get('Date'));

            //Set the amount value based on type
        if(ar.get('Type__c') == 'Budget'){
            budgetAmount = Integer.valueOf(ar.get('Amount'));
        }else if(ar.get('Type__c') == 'Actual'){
            actualAmount = Integer.valueOf(ar.get('Amount'));
        }else{ //Catch all for a type that isn't recognised
            return;
        }

    }

    public Integer getVarianceFYAmount(){
        return actualAmount - budgetAmount;
    }

    public Decimal getVarianceFYPercent(){
        if(budgetAmount != 0){
            return Decimal.valueOf((Double.valueOf(actualAmount) / Double.valueOf(budgetAmount)) * 100).setscale(1);
        } else{
            return 0;
        }
    }
}

}

[SalesForce] SOQL Query to count only Accounts with Opps in both this year and last year

Question 1:

You can just adjust your Where to clause to limit the result to only accounts that have children that match your criteria like this.

Accounts This Year

SELECT ID, 
      (SELECT StageName, Effective_Date__c, Expiration_Date__c 
       FROM Opportunities
       WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
FROM Account where Id In (Select AccountId From Opportunity WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)

Accounts Last year

SELECT ID, 
      (SELECT StageName, Effective_Date__c, Expiration_Date__c 
       FROM Opportunities
       WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
FROM Account where Id In (Select AccountId From Opportunity Where StageName = 'Closed AND Expiration_Date__c < TODAY AND Expiration_Date__c > LAST_365_DAYS)

Question 2

You can get the count like this

Accounts This Year

SELECT count(Id) mycount
    FROM Account where Id In (Select AccountId From Opportunity WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)

Accounts Last Year

SELECT count(Id) mycount
        FROM Account where Id In (Select AccountId From Opportunity Where StageName = 'Closed AND Expiration_Date__c < TODAY AND Expiration_Date__c > LAST_365_DAYS)

Question 3

You can use the same method, just combine the Where clauses from question 1

SELECT ID, 
      (SELECT StageName, Effective_Date__c, Expiration_Date__c 
       FROM Opportunities
       WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
FROM Account where Id In (Select AccountId From Opportunity WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
And Id In (Select AccountId From Opportunity Where StageName = 'Closed AND Expiration_Date__c < TODAY AND Expiration_Date__c > LAST_365_DAYS)

Best Answer

Related Solutions

[SalesForce] Split a single field into 2 columns for a VF page

[SalesForce] SOQL Query to count only Accounts with Opps in both this year and last year

Related Topic