Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

brianrmacdonald
Contributor

Aggregating records when Salesforce query returns multiple records

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.

Community Browser