Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

DIRECT SELECT Problem

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

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

11 Replies
Not applicable
Author

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.

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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

preminqlik
Specialist II
Specialist II

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;

Not applicable
Author

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

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

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

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

Thank you Steve

Chris

Not applicable
Author

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