topic Help understanding key filed joins between tables in SQL database in QlikView

Help understanding key filed joins between tables in SQL database

Wed, 13 Jun 2012 14:43:36 GMT

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

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.

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

Wed, 13 Jun 2012 14:53:35 GMT

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

MayilVahanan — Wed, 13 Jun 2012 15:12:47 GMT

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

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

Wed, 13 Jun 2012 15:16:38 GMT

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

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

Wed, 13 Jun 2012 15:21:00 GMT

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

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

Wed, 13 Jun 2012 15:36:43 GMT

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

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

Wed, 13 Jun 2012 15:38:55 GMT

//-------- 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

MayilVahanan — Wed, 13 Jun 2012 15:41:30 GMT

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 .

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

Wed, 13 Jun 2012 15:56:03 GMT

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

Can't thank everyone enough for the kind assistance.

Darrel