Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Not applicable

Why cannot import joins from OLE DB connection to SQL Server?

I'm working with Qlik Sense and creating a new connection to SQL Server on Azure using OLE DB. But when i go to the data model i can't see any join on selected tables. What's wrong? Thanks.

1 Solution

Accepted Solutions
MVP
MVP

Re: Why cannot import joins from OLE DB connection to SQL Server?

Qlik Sense will connect tables (join if you like) by matching similar named field names from different tables. So if for instance two tables have no commonly named columns which will be turned into fields it will not associate the two tables. They will stay disconnected or un-associated.

Do connect them you will have to rename one or both column names that should be a key by for instance doing like this in the Load Script:

Before

Customers:

LOAD

     CustID,    

     CompanyName

;

SQL SELECT

     *

FROM dbo.Customers;

Sales

     Customer_ID,

     SalesDate,

     Amount

;

SQL SELECT

*

FROM dbo.Sales;

After

Customers:

LOAD

     CustID AS Customer_ID,    

     CompanyName

;

SQL SELECT

     *

FROM dbo.Customers;

Sales

     Customer_ID,

     SalesDate,

     Amount

;

SQL SELECT

*

FROM dbo.Sales;

Then you will get them connected (joined) by the common key called Customer_ID in this case.

7 Replies

Re: Why cannot import joins from OLE DB connection to SQL Server?

Nothing is wrong. The UI allows you to select either tables or views. If you want to load the result of a join between several tables you'll have to create the necessary sql statement manually or load the separat tables into the Qlik Sense app first and join the data there.


talk is cheap, supply exceeds demand
MVP
MVP

Re: Why cannot import joins from OLE DB connection to SQL Server?

Qlik Sense will connect tables (join if you like) by matching similar named field names from different tables. So if for instance two tables have no commonly named columns which will be turned into fields it will not associate the two tables. They will stay disconnected or un-associated.

Do connect them you will have to rename one or both column names that should be a key by for instance doing like this in the Load Script:

Before

Customers:

LOAD

     CustID,    

     CompanyName

;

SQL SELECT

     *

FROM dbo.Customers;

Sales

     Customer_ID,

     SalesDate,

     Amount

;

SQL SELECT

*

FROM dbo.Sales;

After

Customers:

LOAD

     CustID AS Customer_ID,    

     CompanyName

;

SQL SELECT

     *

FROM dbo.Customers;

Sales

     Customer_ID,

     SalesDate,

     Amount

;

SQL SELECT

*

FROM dbo.Sales;

Then you will get them connected (joined) by the common key called Customer_ID in this case.

Not applicable

Re: Why cannot import joins from OLE DB connection to SQL Server?

Thanks a lot, your responses was gret and the last one (petter.skjolden) have the solution i'm looking for. Thanks again!

Not applicable

Re: Why cannot import joins from OLE DB connection to SQL Server?

Why if i have many joins to the same table? Something like that:

LOAD "PFac_idAtencionMedicaKey",

    "PFac_tipoFactorAsociado",

    "PFac_idEncuestaFkey",

    "PFac_identificadorEncuestado",

    "PFac_nombreMedicoRevisoHC",

    "PFac_fechaRevisionHCFkey",

   "PFac_comorbilidad1Fkey" AS AuxFac_idComorbilidadKey,

    "PFac_comorbilidad2Fkey",

    "PFac_comorbilidad3Fkey",

    "PFac_comorbilidad4Fkey";

SQL SELECT "PFac_idAtencionMedicaKey",

    "PFac_tipoFactorAsociado",

    "PFac_idEncuestaFkey",

    "PFac_identificadorEncuestado",

    "PFac_nombreMedicoRevisoHC",

    "PFac_fechaRevisionHCFkey",

    "PFac_comorbilidad1Fkey",

    "PFac_comorbilidad2Fkey",

    "PFac_comorbilidad3Fkey",

    "PFac_comorbilidad4Fkey"

FROM Ecosalud.dbo."PFac_AtencionMedica";

LOAD "AuxFac_idComorbilidadKey",

    "AuxFac_comorbilidad";

SQL SELECT "AuxFac_idComorbilidadKey",

    "AuxFac_comorbilidad"

FROM Ecosalud.dbo."AuxFac_Comorbilidad";

What with the other joins? Thanks again.

MVP
MVP

Re: Why cannot import joins from OLE DB connection to SQL Server?

I am not sure if I understand your question? I can't see many joins it what you show here ... You mean the different PFac_comorbilidad1Fkey, PFac_comorbilidad2Fkey, PFac_comorbilidad3Fkey ... and you want them all to be able to link to the last table ... even though you have selected only one here?

MVP
MVP

Re: Why cannot import joins from OLE DB connection to SQL Server?

Actually ... I think I understand your question now... let me have a few minutes to suggest a solution for you with CROSSTABLE LOAD for the first table....

MVP
MVP

Re: Why cannot import joins from OLE DB connection to SQL Server?

Could you please post this last question as a new question ... so if me suggestion is works for you I have the opportunity to get some points?

Community Browser