joining 2 tables in salesforce during load

[#1]

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

[johnca · Accepted solution]

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.

[#3]

John,

   Thanks, that worked perfectly!

     -Ming

[Anonymous]

Ming,

Would you mind to mark this thread as "answered"  by clicking "correct answer" on John's post?  The reasons:

- The "answerers" don't waste time reading it, thinking it is still open.

- It helps others to find solutions in similar situations.

- Gives deserved credit for the correct answer.  Unless you don't like John for some reason , you can always mark your own reply as "correct" - but you don't get points for this

Regards,

Michael