Table not found for STORE statement

[#1]

Hi,

in my Loader QVW I have written a SQL statement with SELECT ... FROM and WHERE. Then I put the STORE statement to store the table read into a QVD. F.e.

Table1: SQL SELECT Code, Description, Rule FROM myDB.dbo.myTable WHERE myDB.dbo.myTable.Rule = 'A';

STORE Table1 INTO d:\qvdfiles\Tables.QVD;

When I reload the data I have an error message about "Table not found STORE Table1 INTO d:\qvdfiles\Tables.QVD"

In the script I have written other SQL SELECT + STORE statements with success.

Any helps to me to solve this issue, please? Thanks

[Miguel_Angel_Baeyens]

Hello,

Although it's not a must, use always a LOAD statement, that will help you with the formatting, naming of the fields:

Table1:
LOAD Code, 
     Description, 
     Rule;
SQL SELECT Code, Description, Rule
FROM myDB.dbo.myTable
WHERE myDB.dbo.myTable.Rule = 'A'; 
STORE Table1 INTO File.qvd;

When that error is thrown, it may be because a implicit concatenation, meaning that the table previously loaded in the script has the same exact number and name of fields, and QlikView understand, unless stated in contrary, that both tables are the same, concatenating them in memory.

One way to avoid this is using the NOCONCATENATE keyword before the LOAD statement.

Check as well that you don't have two tables named alike. In that case, the resulting table will be named Table1-1 (you can check that easily in the table viewer CTRL + T).

Hope that helps.

[#3]

Thanks Miguel,

in fact before Table1 definition there is Table2 definition with the SQL statement and STORE but on Table2. For the Table2 I have used the same field name:Code, Description and Rule. So I have put an alias only for Code in the Table1 definition:

Table1: SQL SELECT Code as MyCode, Description, Rule FROM myDB.dbo.myTable WHERE myDB.dbo.myTable.Rule = 'A'

I haven't put any alias for Description and Rule fields. The load is executed successfully but it is yet in progress (???): it is strange!

I think to use the LOAD statement into the QVW that consumes the QVD files.

Thanks

[Miguel_Angel_Baeyens]

Hello,

Check the following example:

Table1:LOAD Code, Name;SQL SELECT Code, Name FROM Database1.Table1; STORE Table1 INTO File_Table1.qvd; // This will store all records in Table1 Table2:NOCONCATENATE LOAD Code, Name;SQL SELECT Code, Name FROM Database2.Table2;STORE Table2 INTO File_Table2.qvd; // This will work also Table3:LOAD Code, Name;SQL SELECT Code, Name FROM Database3.Table3; // This will not workSTORE Table3 INTO File_Table3.qvd;

The reason is that Table3 doesn't exist for QlikView, that has "appended" all records from Database3.Table3 to the Table2, even when you have set a different label for it. That's called implicit or automatic concatenation.

Moreover, since you have two tables (Table1 and Table2 -that contains all records from Database3.Table3) with two fields named alike, it will create a unwanted synthetic key or even a loop, depending on your datamodel. This happens when the script has finished pulling records but before ending the execution, when QlikView creates the associations between tables through its common names. That's likely the reason to be "in progress" after loading.

Regards.

[#5]

Hi Miguel.

Sorry, but I have some doubts!

In this case, what is the reason to use LOAD statement before the SQL statement? I think to use LOAD in the document that reads the QVD files.

As you say, I have a Table1 and Table2 derived from the same Table3, but I have put a different alias for the primary key of Table1 and Table2, and so Table1 and Table2 shouldn't be considered as the same table. Also in case of unwanted association, in the script are present other tables with wanted association (by naming field) and the load data finishes. Moreover, Table1 and Table2 have wanted association (o relation) to other table and so I cannot renaming some fields.

Thanks

[Miguel_Angel_Baeyens]

Hello,

pscorca69 wrote:In this case, what is the reason to use LOAD statement before the SQL statement? I think to use LOAD in the document that reads the QVD files.

This has been discussed in the forums. Take a look at this post, among others, to see the difference between LOAD and SELECT. But in addition to what I already mentioned, and making it short: SELECT pulls from the database (output) and LOAD puts into memory (input) offering total control on what and how the data is dealt.

pscorca69 wrote:As you say, I have a Table1 and Table2 derived from the same Table3, but I have put a different alias for the primary key of Table1 and Table2, and so Table1 and Table2 shouldn't be considered as the same table. Also in case of unwanted association, in the script are present other tables with wanted association (by naming field) and the load data finishes. Moreover, Table1 and Table2 have wanted association (o relation) to other table and so I cannot renaming some fields.

Can you post your script so we can check it further?

Regards.

[#7]

Hi Miguel, I have read the suggested post and I want to re-read better.

I follow this methodology: before load data into QVD files, in a loader qvw, and then, in another qvw file, I load the data (with the LOAD statement) from the QVD files to put them onto tables, charts, etc. Why using the LOAD statement two times? Also with SQL Select I can use f.e. the alias to rename fields. If I use the LOAD before SQL SELECT into the loader qvw, why put the data into QVD or using another QV document (different from the loader) to LOAD the data from the QVD files?

I have added an alias for Commesse table and the data load functions. However I have attached the app.

Thanks

[Miguel_Angel_Baeyens]

Hello,

It's true that there are some functions you can use in the driver to pass on to the database, but usually these are a reduced set of the allowed by the database manager and, in any case, it will not allow you to format dates for QlikView, apply maps, lookups, peeks, some kinds of aggregation... that QlikView does. That is the sense of the LOAD actually, putting into memory some records as needed by the charts where they're going to be used. But again, it's not a must, so you can skip this step.

On the other hand, I always (as often as I can) do all transformations needed in the LOAD corresponding to the Select and always before the STORE, so the next LOAD will be optimized, and thus, extremely faster. Of course, sometimes it simply is not possible (think of concatenation, for example). My personal thought is that all you can do in the script will save time of development and chart rendering.

In regards to your file, I certainly see lots of synthetic keys. Do you want to create them?

I don't know which table is not being loaded (I cannot reload since I cannot connect to your database). Can you please specify?

Regards.

[#9]

Hi Miguel,

when you say:

"On the other hand, I always (as often as I can) do all transformations needed in the LOAD corresponding to the Select and always before the STORE, so the next LOAD will be optimized, and thus, extremely faster. Of course, sometimes it simply is not possible (think of concatenation, for example). My personal thought is that all you can do in the script will save time of development and chart rendering."

means that you use LOAD before SELECT and STORE into a QVD file also simply to use an alias for a field in order to create a wanted association or when with LOAD it is managed cases more complex f.e aggregations.

F.e.:

a) LOAD myCode

Table1:

SQL SELECT code as myCode FROM myDb.dbo.myTable

STORE Table1 INTO C:\myQVapps\Tables.QVD

LOAD myCode FROM C:\myQVapps\Tables.QVD

or

b) LOAD mySum

Table1:

SQL SELECT sum(myAmount) as mySum FROM myDb.dbo.myTable

STORE Table1 INTO C:\myQVapps\Tables.QVD

LOAD myCode FROM C:\myQVapps\Tables.QVD

Moreover, I have solved the not-terminating data load by put an alias (CodiceBU) for BURiferimento field for Commesse table. I think the syn keys are generated to have the wanted relation between tables. I think that they are necessary, isn'it? Do you want the SQL Server db? It contains some record for the tables.

Thanks

[Miguel_Angel_Baeyens]

Hi,

Syntax for LOAD is a bit different, but in short what I mean, following your example is

Table1: // First, the table labelLOAD code AS myCode; // renaming fields as they come, in the LOAD part, not in the SQLSQL SELECT code FROM myDb.dbo.myTable STORE Table1 INTO C:\myQVapps\Tables.QVD; DROP TABLE Table1; // More script here Table1: // Useful to identify what you are loadingLOAD myCode FROM C:\myQVapps\Tables.QVD (qvd);

I'm glad you have solved your problem, and that the synthetic keys are wanted and worked as expected. They are necessary provided there is a correspondence (value to value) between tables. It like creating a composite key using more than one field. A synthetic key is created as soon as QlikView has two tables (or more) with two fields (or more) named alike.

Regards.