topic Re: DIRECT SELECT Problem in QlikView

DIRECT SELECT Problem

Fri, 08 Nov 2013 08:57:02 GMT

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

Fri, 08 Nov 2013 09:12:36 GMT

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

stevedark — Fri, 08 Nov 2013 09:14:43 GMT

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

preminqlik — Fri, 08 Nov 2013 09:52:12 GMT

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

Fri, 08 Nov 2013 10:00:02 GMT

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

stevedark — Fri, 08 Nov 2013 10:41:19 GMT

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

Fri, 08 Nov 2013 10:57:09 GMT

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

stevedark — Fri, 08 Nov 2013 11:54:19 GMT

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

Fri, 08 Nov 2013 15:38:59 GMT

Thank you Steve

Chris

Re: DIRECT SELECT Problem

Mon, 20 Jan 2014 13:04:54 GMT

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

Re: DIRECT SELECT Problem

Tue, 21 Jan 2014 11:41:23 GMT

You can include as many connections (ODBC, OLEDB, etc.) as you want.

For Direct Discovery QlikView takes the previous one to the DIRECT sentence to run the queries during analysis time.

Re: DIRECT SELECT Problem

Tue, 21 Jan 2014 13:10:13 GMT

Muchas gracias Juan

Hasta la vista

Chris