11 Replies Latest reply: Mar 18, 2011 1:33 PM by Miguel Angel Baeyens de Arce RSS

    Table not found for STORE statement

      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

        • Re: Table not found for STORE statement
          Miguel Angel Baeyens de Arce

          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.

            • Table not found for STORE statement

              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

                • Table not found for STORE statement
                  Miguel Angel Baeyens de Arce

                  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.

                    • Table not found for STORE statement

                      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

                        • Table not found for STORE statement
                          Miguel Angel Baeyens de Arce

                          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.

                           

                            • Table not found for STORE statement

                              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

                                • Table not found for STORE statement
                                  Miguel Angel Baeyens de Arce

                                  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.

                                    • Table not found for STORE statement

                                      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

                                        • Table not found for STORE statement
                                          Miguel Angel Baeyens de Arce

                                          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.

                                            • Table not found for STORE statement

                                              Hi Miguel.

                                              I my reply I want to underline the importance of LOAD when it is used to create an aggregation rather than an alias to rename a field maintaning the original name in the SQL SELECT. However, it is clear, I will use the LOAD to get data from the QVD files. It seems less important to use LOAD before SQL SELECT only to rename a field with an alias.

                                              Thanks

                                                • Table not found for STORE statement
                                                  Miguel Angel Baeyens de Arce

                                                  Hi,

                                                  Yes, why not.

                                                  You can code as you want. And indeed, using LOAD just for renaming (even more only for aggregating) is quite a poor use of the power of the LOAD statement. Anyway, QlikView will do that for you even if you don't use it. But if you take a look at the code samples posted in the forums, everyone in any moment needs some transformation that it's just no possible in the database or not supported by the ODBC. There is where LOAD allows you to control how the data is loaded, leaving the SQL as plain as possible so the Server is less stressed.

                                                  It's easier for me to do all changes in a load, so the information eventually stored in the QVD will not need any further change, and so the optimized load of QVD files works fully.

                                                  Regards.