7 Replies Latest reply: May 7, 2015 5:45 PM by Petter Skjolden RSS

    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.

        • Re: Why cannot import joins from OLE DB connection to SQL Server?
          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.

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

            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?

              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.