topic Re: Why cannot import joins from OLE DB connection to SQL Server? in Connectivity & Data Prep

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

Thu, 07 May 2015 03:23:20 GMT

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.

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

Gysbert_Wassenaar — Thu, 07 May 2015 16:17:55 GMT

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.

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

petter — Thu, 07 May 2015 17:19:02 GMT

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.

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

Thu, 07 May 2015 20:38:15 GMT

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

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

Thu, 07 May 2015 21:38:33 GMT

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.

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

petter — Thu, 07 May 2015 21:42:39 GMT

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?

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

petter — Thu, 07 May 2015 21:44:07 GMT

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

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

petter — Thu, 07 May 2015 21:45:14 GMT

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?