3 Replies Latest reply: Dec 17, 2013 1:54 PM by Michael Solomovich RSS

    joining 2 tables in salesforce during load

      I'm new to Qlikview  and am bring in data from salesforce, The account and opportunity tables, I just need the location field from account.


      This is what I have so far,  but would like to join that location field, I like to add a join before the 'WHERE' section, but can't seem to do joins here.



      LOAD Id AS Opp_Id,

        AccountId AS AccountId,

        Amount AS Opp_Amount,

        CloseDate AS Opp_CloseDate,

        date(CloseDate) AS PaymentDate,

        year(CloseDate) AS PaymentYear,

        month(CloseDate) AS PaymentMonth,


      SQL SELECT * FROM Opportunity__c

      WHERE (Commissionable_Booking__c = true);



      LOAD Id AS AccountId,


                ApplyMap('Map_Ent_Region',Location__c,'Unknown') as Region;

      SQL SELECT *

      FROM Account;



      Any help would be much appreciated.



        • Re: joining 2 tables in salesforce during load
          John Cavoulas

          I do this all the time and, although I don't see the same fields in the Account as you (I used BillingState rather than Location__c and my Opportunity__c table is called plainly Opportunity) I am able to add the Billing City to the opportunities using a Left Join a la...




               Id as Opp_Id,


               Amount as Opp_Amount,

               CloseDate as Opp_CloseDate,

               Date(ClosedDate) as PaymentDate,

               Year(CloseDate) as PaymentYear,

               Month(CloseDate) as PaymentMonth;

          SQL Select * From Opportunity

          Where AccountId != Null

          And Amount != Null;



          Left Join



               Id as AccountId;

          SQL Select



          From Account

          Where Id In (Select AccountId From Opportunity);


          I added the Where clauses after thinking and seeing there were some null data. You may not want that.