Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Help understanding key filed joins between tables in SQL database

I am looking for some help in understanding the key field joins between tables in an SQL database that is connected to Qlikview.  We have an SQL database with tables that are all joined by a common key field named PDOCSNUMBER.  This field contains a unique key that links the fields together. 

My script looks like this:

ODBC

CONNECT TO EmergencyPro;

//-------- Start Multiple Select Statements ------

SQL SELECT PCOCSNUMBER

FROM EmergencyPro.dbo."tbl_INCIDENT_DATA_2";



SQL SELECT "NOC_AT_SCENE"

FROM EmergencyPro.dbo."tbl_INCIDENT_DATA_2";



SQL SELECT PCOCSNUMBER

FROM EmergencyPro.dbo."tbl_MEDICATIONS";



SQL SELECT MEDICATION

FROM EmergencyPro.dbo."tbl_MEDICATIONS";



SQL SELECT PCOCSNUMBER

FROM EmergencyPro.dbo."tbl_INTERVENTIONS";



SQL SELECT "TYPE_INTERVENTION"

FROM EmergencyPro.dbo."tbl_INTERVENTIONS";

//-------- End Multiple Select Statements ------

TableView.PNG

However, the three different tables are not being associated (joined) by Qlikview when I look at the table view.  I am under the assumption that because the tables contain a commonly named field, they will automatically be associated.

Any advice of help you can offer would be appreciated.

1 Solution

Accepted Solutions

Re: Help understanding key filed joins between tables in SQL database

Hi,

     Try like this,

ODBC

CONNECT TO EmergencyPro;

//-------- Start Multiple Select Statements ------

SQL SELECT PCOCSNUMBER, "NOC_AT_SCENE"

FROM EmergencyPro.dbo."tbl_INCIDENT_DATA_2";


SQL SELECT PCOCSNUMBER, MEDICATION

FROM EmergencyPro.dbo."tbl_MEDICATIONS";



SQL SELECT PCOCSNUMBER,"TYPE_INTERVENTION"

FROM EmergencyPro.dbo."tbl_INTERVENTIONS";

     By using this, it joins three table automatically.


Hope it helps .

8 Replies
Not applicable

Re: Help understanding key filed joins between tables in SQL database

You are correct, Qlikview joins fields using like name fields so you could alias your fields in the load e.g.

ODBC

CONNECT TO EmergencyPro;

//-------- Start Multiple Select Statements ------

LOAD PCOCSNUMBER AS PRIMARYKEY;

SQL SELECT PCOCSNUMBER

FROM EmergencyPro.dbo."tbl_INCIDENT_DATA_2";

Re: Help understanding key filed joins between tables in SQL database

hi,

     Qlikview joins the table based on the common fields in tables, so as you mentioned earlier, joined all the table using common field as PSCOCSNUMBER in sql. Use same field in qlikview while loading, so it joins all the tables automatically.

Hope it helps

Not applicable

Re: Help understanding key filed joins between tables in SQL database

I have added the line to the script and reloaded:

ODBC

CONNECT TO EmergencyPro;

LOAD PCOCSNUMBER AS PRIMARYKEY;

//-------- Start Multiple Select Statements ------

SQL SELECT PCOCSNUMBER

FROM EmergencyPro.dbo."tbl_INCIDENT_DATA_2";



SQL SELECT "NOC_AT_SCENE"

FROM EmergencyPro.dbo."tbl_INCIDENT_DATA_2";



SQL SELECT PCOCSNUMBER

FROM EmergencyPro.dbo."tbl_MEDICATIONS";



SQL SELECT MEDICATION

FROM EmergencyPro.dbo."tbl_MEDICATIONS";



SQL SELECT PCOCSNUMBER

FROM EmergencyPro.dbo."tbl_INTERVENTIONS";



SQL SELECT "TYPE_INTERVENTION"

FROM EmergencyPro.dbo."tbl_INTERVENTIONS";

//-------- End Multiple Select Statements ------

It doesn not seem to have made a difference. Any ideas on why are the tables not being joined together and what can I do about that?

THX

Not applicable

Re: Help understanding key filed joins between tables in SQL database

You have not named your fields the same, I would use the example above in my previous comment to build your script out a little:

LOAD [FIELD] AS PRIMARYKEY;

SQL SELECT [FIELD] FROM [DBO]

Do that for each statement and it should join nicely

Not applicable

Re: Help understanding key filed joins between tables in SQL database

So my script looks like this now:

ODBC

CONNECT TO EmergencyPro;



//-------- Start Multiple Select Statements ------

LOAD PCOCSNUMBER AS PRIMARYKEY;

SQL SELECT PCOCSNUMBER

FROM EmergencyPro.dbo."tbl_INCIDENT_DATA_2";



SQL SELECT "NOC_AT_SCENE"

FROM EmergencyPro.dbo."tbl_INCIDENT_DATA_2";



LOAD PCOCSNUMBER AS PRIMARYKEY;

SQL SELECT PCOCSNUMBER

FROM EmergencyPro.dbo."tbl_MEDICATIONS";



SQL SELECT MEDICATION

FROM EmergencyPro.dbo."tbl_MEDICATIONS";



LOAD PCOCSNUMBER AS PRIMARYKEY;

SQL SELECT PCOCSNUMBER

FROM EmergencyPro.dbo."tbl_INTERVENTIONS";



SQL SELECT "TYPE_INTERVENTION"

FROM EmergencyPro.dbo."tbl_INTERVENTIONS";

//-------- End Multiple Select Statements ------

Yet they are still not joining... 

I am sorry - I am a better fireman than computer guy..

Not applicable

Re: Help understanding key filed joins between tables in SQL database

//-------- Start Multiple Select Statements ------

LOAD PCOCSNUMBER AS PRIMARYKEY;

SQL SELECT PCOCSNUMBER

FROM EmergencyPro.dbo."tbl_INCIDENT_DATA_2";


LOAD NOC_AT_SCENE AS PRIMARYKEY;
SQL SELECT "NOC_AT_SCENE"

FROM EmergencyPro.dbo."tbl_INCIDENT_DATA_2";



LOAD PCOCSNUMBER AS PRIMARYKEY;

SQL SELECT PCOCSNUMBER

FROM EmergencyPro.dbo."tbl_MEDICATIONS";


LOAD MEDICATION AS PRIMARYKEY;
SQL SELECT MEDICATION

FROM EmergencyPro.dbo."tbl_MEDICATIONS";



LOAD PCOCSNUMBER AS PRIMARYKEY;

SQL SELECT PCOCSNUMBER

FROM EmergencyPro.dbo."tbl_INTERVENTIONS";


LOAD TYPE_INTERVENTION AS PRIMARYKEY;
SQL SELECT "TYPE_INTERVENTION"

FROM EmergencyPro.dbo."tbl_INTERVENTIONS";

Re: Help understanding key filed joins between tables in SQL database

Hi,

     Try like this,

ODBC

CONNECT TO EmergencyPro;

//-------- Start Multiple Select Statements ------

SQL SELECT PCOCSNUMBER, "NOC_AT_SCENE"

FROM EmergencyPro.dbo."tbl_INCIDENT_DATA_2";


SQL SELECT PCOCSNUMBER, MEDICATION

FROM EmergencyPro.dbo."tbl_MEDICATIONS";



SQL SELECT PCOCSNUMBER,"TYPE_INTERVENTION"

FROM EmergencyPro.dbo."tbl_INTERVENTIONS";

     By using this, it joins three table automatically.


Hope it helps .

Not applicable

Re: Help understanding key filed joins between tables in SQL database

That worked.... THANKS...  This will help me apply the concept to other tables..

Can't thank everyone enough for the kind assistance.

Darrel