11 Replies Latest reply: Jan 21, 2014 8:10 AM by christian juillard RSS

    DIRECT SELECT Problem

    christian juillard

      Hi everybody,

       

      who can tell me why a so simple script doesn't load anything from "Test.Employes" table ?

       

      ODBC CONNECT32 TO phpmyadmin;          //  MySQL  Database

       

      DIRECT SELECT

          nom            as Nom_FromTest,

          matricule    as %E,

          matricule,

          `date_in`,

          `date_out`

      FROM test.employes;

       

      Emp:

      LOAD    EmpID            as %E,     

              EmpID,     

              Prénom,     

              Nom,     

              Fonction

      FROM emp.xls

      (biff, embedded labels, table is [Sheet1$]);

       

       

      Here is the log file where we can see no lines are loaded from DIRECT TABLE.  It should load 2 lines

       

      08/11/2013 09:50:28:       DIRECT SELECT

      08/11/2013 09:50:28:           nom            as Nom_FromTest,

      08/11/2013 09:50:28:           matricule    as %E,

      08/11/2013 09:50:28:           matricule,

      08/11/2013 09:50:28:           `date_in`,

      08/11/2013 09:50:28:           `date_out`

      08/11/2013 09:50:28:       FROM test.employes

      08/11/2013 09:50:28:           5 champs trouvés: Nom_FromTest, %E, matricule, date_in, date_out, 0 lignes récupérées

       

      Emp table loads correctly

       

      QV Version : 11.20.12018.0 SR3  64bits

       

      thank you for your help

      Chris

        • Re: DIRECT SELECT Problem
          Juan Gerardo Cabeza Luque

          Direct Discovery doesn't load any record from the Direct table. It just examines the fields you write down in this Select only for associating with other tables in your model.

           

          The Direct table is created, but empty. During execution, any time QlikView needs the values it runs a SELECT statement against the ODBC to retrieve the needed records. But during reload, there is no records to load.

          • Re: DIRECT SELECT Problem
            Steve Dark

            Hi Christian,

             

            I presume that your employee table is not massive - in which case you probably don't want to be doing a DIRECT select from it.

             

            This keyword sets the table up to be used in direct discovery mode - that is that the data from the table is retrieved when the user is using the application rather than at load time.

             

            If you are not intending this to be the case for employees remove the DIRECT prefix and see if that then gives the results you are expecting.

             

            Regards,

            Steve

              • Re: DIRECT SELECT Problem
                Prem Kumar Thangallapally

                hi there,

                SELECT statement against the ODBC to retrieve the needed records. But during reload, there is no records to load.

                 

                 

                 

                 

                 

                use preceding load as below:

                 

                LOAD

                nom            as Nom_FromTest,

                  matricule    as %E,

                    matricule,

                    `date_in`,

                    `date_out`

                SELECT *

                FROM test.employes;

                • Re: DIRECT SELECT Problem
                  christian juillard

                  Thank you guys for your answers.

                   

                  Yes my table are very small, because it is just for testing how it works.

                   

                  So if I understand well I can never see data in table but only as expressions ?

                   

                  best regards

                  Chris

                    • Re: DIRECT SELECT Problem
                      Steve Dark

                      The DIRECT statement is only required for massive tables, where you don't want to store all of the information in memory.  Unless you are talking more than tens of millions of employees you don't need to be using it there!

                       

                      Without the DIRECT prefix you should get the data you require.

                       

                      It looks like you may have a bug in your script though, where you have Date In and Date Out in single quotes.  These will be treated as literal strings without column names, and two unnamed columns will probably cause QlikView to bail.  You are probably wanting to use square brackets around the field name instead?

                       

                      Steve

                        • Re: DIRECT SELECT Problem
                          christian juillard

                          Hi Steve

                           

                          Thank you but there is no bug.

                          Everything works well (better than my brain).

                          I thought i could see raw data, but according to Juan's explainations, i understood it is not possible.

                          However i can count any data coming from Direct table using data from Emp table as dimensions.

                          I understood it is for a big amount of data but i was just testing.

                           

                          best regards

                          Chris

                            • Re: DIRECT SELECT Problem
                              Steve Dark

                              Hi Chris,

                               

                              Behind the scenes QlikView will be getting a distinct list of key values from the underlying table to join the data that will be pulled directly into the rest of your in memory data model.  I'm not sure if you can query that in order to get a count of rows.

                               

                              The simplest route would be to push the query back to the database and simply do a count there:

                               

                              EmpRowCount:

                              LOAD
                                   EmpRowCount

                                   ;

                              SQL SELECT

                                   COUNT(*) as EmpRowCount

                              FROM test.employes;

                               

                              You can then use PEEK to put that into a variable, if required.

                               

                              Steve

                      • Re: DIRECT SELECT Problem
                        christian juillard

                        Hola Juan

                         

                        I've got an additive question to this.

                        If QV runs ODBC while it needs to fetch the data on demand, does this mean we can store only 1 ODBC connection string into the script ? Or does QV takes the last one or associate previous ODBC with next DIRECT SELECT ?

                         

                        Best regards

                        Chris