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.

       

      Opportunity:

      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);

       

      Account:

      LOAD Id AS AccountId,

                Location__c,

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

      SQL SELECT *

      FROM Account;

       

       

      Any help would be much appreciated.

      Thanks.

      -Ming

        • 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...

           

          Opportunity:

          Load

               Id as Opp_Id,

               AccountId,

               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;

           

          Account:

          Left Join

          Load

               BillingState,

               Id as AccountId;

          SQL Select

               BillingState,

               Id

          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.