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

[#1]

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.

[Gysbert_Wassenaar]

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.

[petter · Accepted solution]

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.

[#4]

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

[#5]

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.

[petter]

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?

[petter]

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

[petter]

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?