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


           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:


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



      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:


      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




      and here is what I get:


      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.