0 Replies Latest reply: Dec 28, 2017 5:33 PM by Brian MacDonald RSS

    Aggregating records when Salesforce query returns multiple records

    Brian MacDonald

      So I have a Salesforce query that looks like this:

       

      SELECT Id

      , r_physician_id__r.LastName

      FROM

           blah, blah, blah,

       

      The field in the select statement is a lookup relationship in Salesforce.  In the SF Developers console the results look like this:

      2017-12-28_16-16-53.gif

      However when I retrieve the same results via the SF Connector in QS Enterprise I get this:

      2017-12-28_16-09-30.gif

       

      I suspect the reason for the difference is that the lookup relationship is SF is doing sort of an outer join resulting in one null record and one with the data I am after.  I don't know if there is anything I can do to the SF query to make it work right.

       

      So I was trying to correct for this in the load script and I cannot figure out how to merge the records.  Here is what I am trying:

      Load

      r_reeg__c.Id AS peer_id

          , MaxString(r_reeg__c.r_physician_id__r.LastName) AS physician_lastname

          WHERE r_reeg__c.Id <> Null()

          GROUP BY

          r_reeg__c.Id    

          ;

       

      and here is what I get:

      2017-12-28_16-25-53.gif

      I have tried all of the string aggregation functions to no avail.

       

      How can I aggregate the data so as to eliminate the null values?

       

      Any help would be greatly appreciated.