Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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
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;
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
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
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
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
Thank you Steve
Chris
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