3 Replies Latest reply: Jun 23, 2014 11:27 AM by Goran Korsgren RSS

    Drop All Tables Except

    Mike Swinn

      I have a very large qlikview workbook with many tables which performs a number of transforms on my data. I am creating a condensed version of this to load from the first which is essentially going to be a cut down and somewhat aggregated version, and I've started with a binary load. Of course this loads all tables from the first app, most of which I don't want.

       

      Is there any simple to effective run a 'Drop All tables Except A, B and C' for example?

       

      If not directly would it be possible through a simple loop script, or is there another way I could approach loading data in an optimised manner, to only take the tables I want?

        • Re: Drop All Tables Except
          Björn Miessen

          The following statement drops all tables, you could create an if statement inside the loop for the exceptions you have;

           

          vTables = TableName(0);

          FOR i = 1 to NoOfTables() - 1    

            LET vTables = vTables & ', ' & TableName(i);

          NEXT DROP Tables $(vTables);

            • Re: Drop All Tables Except
              Mike Swinn

              Perfect. Would it be possible to import the exceptions from a csv list and do a kind of if not in()... statement to ignore them?

                • Re: Drop All Tables Except
                  Goran Korsgren

                  I actually created a sub for this:

                   

                   

                  sub dropTablesExcept(dropTablesExcept_exceptList,dropTablesExcept_n,dropTablesExcept_noOfTables,dropTablesExcept_tableName,dropTablesExcept_excempt,dropTablesExcept_except)

                  /*

                  Drops all currently existing tables except those you have listed in the first parameter.

                  Note that the rest of the parameters should not be set at the call

                   

                  Usage example:

                  // Drop all except two tables:

                  Set exceptList="'ASCII','Characters'";

                   

                  call dropTablesExcept(exceptList);

                   

                  //Another example, drop all except one table:

                  Set exceptList="'ASCII'";

                   

                  call dropTablesExcept(exceptList);

                  */

                      Let dropTablesExcept_noOfTables=NoOfTables();

                     

                      FOR dropTablesExcept_n=0 to dropTablesExcept_noOfTables-1

                          Let dropTablesExcept_tableName=TableName(dropTablesExcept_n);

                          Let dropTablesExcept_excempt=False();

                          for Each dropTablesExcept_except in $(dropTablesExcept_exceptList)

                              if dropTablesExcept_tableName=dropTablesExcept_except then

                                  Let dropTablesExcept_excempt=True();

                                  EXIT For

                              ENDIF

                          NEXT

                             

                          IF not dropTablesExcept_excempt then

                              trace DROP Table $(dropTablesExcept_tableName);

                              DROP Table $(dropTablesExcept_tableName);

                          ENDIF

                      NEXT

                  endsub

                   

                  /gg