Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Drop All Tables Except

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?

1 Solution

Accepted Solutions
gandalfgray
Specialist II
Specialist II

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

View solution in original post

4 Replies
Not applicable
Author

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);

Not applicable
Author

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?

gandalfgray
Specialist II
Specialist II

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