Reply
Copper Contributor
paulyeo11
Posts: 84
Country: Singapore
Message 1 of 12 (2,590 Views)

How to link the 2 table opportunity table with company table ?

Hi All ,

 

I have try  for many hour and days , till now i still have not able to get the solution , Hope some kind soul can tell me it is possible ?

 

i have using Qlikview to access ACT data file.

 

i manage to get the following 4 table :-

 

"VRP_OPPORTUNITY_PRODUCT"

"VRP_CONTACT_OPPORTUNITY"

"VRP_COMPANY"

"VRP_CONTACT"

 

I manage to link the below 2 table :-    "COMPANY COMPANYID" as ID,

 

"VRP_COMPANY"

"VRP_CONTACT"

 

I also manage to link the below 2 table :-     "OPPORTUNITY PRODUCT OPPORTUNITYID" as ID1,

 

"VRP_OPPORTUNITY_PRODUCT"

"VRP_CONTACT_OPPORTUNITY"

 

 But i cannot link all table , because the opportunity table does not have " company " variable .

 

Hope some one advise me.

 

PAul Yeo

 

 

Please use plain text.
Bronze Super Contributor
vivek
Posts: 1,732
Country: United_Kingdom
Message 2 of 12 (2,529 Views)

Re: How to link the 2 table opportunity table with company table ?

Hiya Paulyeo11,

 

My SQL is pretty weak so this might not be the most optimised query and may need optimising!

 

Select
  VRP_COMPANY.[COMPANY Company] As Company,
  VRP_CONTACT.[CONTACT Contact] As Contact,
  VRP_CONTACT_OPPORTUNITY.[OPPORTUNITY Opportunity Name] As [Opp Name],
  VRP_OPPORTUNITY_PRODUCT.[OPPORTUNITY PRODUCT Product Name] As Product
From
  VRP_OPPORTUNITY_PRODUCT Inner Join
  VRP_CONTACT_OPPORTUNITY On VRP_CONTACT_OPPORTUNITY.[OPPORTUNITY OPPORTUNITYID]
    = VRP_OPPORTUNITY_PRODUCT.[OPPORTUNITY PRODUCT OPPORTUNITYID] Inner Join
  VRP_CONTACT On VRP_CONTACT_OPPORTUNITY.[CONTACT OPPORTUNITY CONTACTID] =
    VRP_CONTACT.[CONTACT CONTACTID] Inner Join
  VRP_COMPANY On VRP_CONTACT.[CONTACT COMPANYID] =
    VRP_COMPANY.[COMPANY COMPANYID]
Group By
  VRP_COMPANY.[COMPANY Company], VRP_CONTACT.[CONTACT Contact],
  VRP_CONTACT_OPPORTUNITY.[OPPORTUNITY Opportunity Name],
  VRP_OPPORTUNITY_PRODUCT.[OPPORTUNITY PRODUCT Product Name]
Order By
  VRP_COMPANY.[COMPANY Company], VRP_CONTACT.[CONTACT Contact],
  VRP_CONTACT_OPPORTUNITY.[OPPORTUNITY Opportunity Name],
  VRP_OPPORTUNITY_PRODUCT.[OPPORTUNITY PRODUCT Product Name]

 

HTH

Vivek

Vivek Gargav
Caldere Associates Ltd.
www.caldere.com
vgargav@caldere.com
My Blog
Please use plain text.
Copper Contributor
paulyeo11
Posts: 84
Country: Singapore
Message 3 of 12 (3,011 Views)

Re: How to link the 2 table opportunity table with company table ?

Hi sir , do i need to buy act reader in order to be able to using SQL to link both table ? I am using ACT win Ver 10. understand that ACT reader will be releasr on april 2009 , and can only work with ACT ver 11. 

 

 

 

Paul

 

Please use plain text.
Copper Contributor
paulyeo11
Posts: 84
Country: Singapore
Message 4 of 12 (2,978 Views)

Re: How to link the 2 table opportunity table with company table ?

Hi All ,

 

Hope some one can help me , as i just  need to know it is posible to link between opportunity table with contact table ? as i am getting stuck with this cannot move on. pls help, 

 

Paul Yeo

 

Please use plain text.
Copper Super Contributor
Posts: 138
Country: United States
Message 5 of 12 (2,950 Views)

Re: How to link the 2 table opportunity table with company table ?

QBE snapshotPaul, the tables you're referencing that start with "VRP" are actually database Views.  They are accessible via the free ACT! OLE/DB Reporting Provider that is included, and installed, with every copy of ACT!.  So it is free essentially.

 

The ACTREADER utility and account allows read-only access to the bare database Tables to which you would need to construct your own security to filter records as desired.

 

To JOIN the Contact record (VRP_CONTACT) to the Opportunity record (VRP_CONTACT_OPPORTUNITY) would look like:

 

Select
  VRP_CONTACT_OPPORTUNITY.[OPPORTUNITY Opportunity Name],
  VRP_CONTACT_OPPORTUNITY.[OPPORTUNITY Status],
  VRP_CONTACT_OPPORTUNITY.[OPPORTUNITY Total],
  VRP_CONTACT.[CONTACT Contact],
  VRP_CONTACT.[CONTACT Company]
From
  VRP_CONTACT_OPPORTUNITY Inner Join
  VRP_CONTACT On VRP_CONTACT.[CONTACT CONTACTID] =
    VRP_CONTACT_OPPORTUNITY.[CONTACT OPPORTUNITY CONTACTID]

 

 

Hope this helps.

Bill Blakey
ACT! Development Team
Sage Software

Please use plain text.
Copper Contributor
paulyeo11
Posts: 84
Country: Singapore
Message 6 of 12 (2,925 Views)

Re: How to link the 2 table opportunity table with company table ?

Hi Bill,

 

Thank you , finally it work now. Thank you very much for your help. This forum help me a lot.

 

Paul

 

Please use plain text.
Copper Contributor
paulyeo11
Posts: 84
Country: Singapore
Message 7 of 12 (2,924 Views)

Re: How to link the 2 table opportunity table with company table ?

Hi Bill ,

 

Now i seek your help one more time , that is i need to link the opportunity table with opportunity product table.

  

Can you pls let me know which are the variable name that i can use to link these 2 table ?

  

Paul Yeo

Please use plain text.
Copper Contributor
paulyeo11
Posts: 84
Country: Singapore
Message 8 of 12 (2,844 Views)

Re: How to link the 2 table opportunity table with company table ?

Hi Bill, Thank you very much , after i got your reply , now i am able to link the contact table with opportunity table. 

 

I seek your help , as now need to link contact with meeting , todo , and call table. can you please give me the variable . the reasons is i need to know how often  my sales staff have make how many meeting per month.

 

 

 

 

Paul

 

Please use plain text.
Copper Super Contributor
Posts: 138
Country: United States
Message 9 of 12 (2,825 Views)

Re: How to link the 2 table opportunity table with company table ?

You want to LEFT OUTER JOIN from VRP_CONTACT_OPPORTUNITY to VRP_OPPORTUNITY_PRODUCT on:

 

VRP_CONTACT_OPPORTUNITY.[OPPORTUNITY OPPORTUNITYID] = VRP_OPPORTUNITY_PRODUCT.[OPPORTUNITY PRODUCT OPPORTUNITYID]

 

Do the LEFT join as the relationship between Opportunity Product and Product is optional.

Bill Blakey
ACT! Development Team
Sage Software

Please use plain text.
Copper Super Contributor
Posts: 138
Country: United States
Message 10 of 12 (2,824 Views)

Re: How to link the 2 table opportunity table with company table ?

Unfortunately you cannot report on Activities as these are not exposed in the ACT! OLEDB Reporting Provider 1.0.

Bill Blakey
ACT! Development Team
Sage Software

Please use plain text.