Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

Drop Field and Drop Table

The Drop script keyword is often used in scripts to drop a field or a table from the data model and from memory.  In this blog, I will discuss some of ways I use Drop Field and Drop Table in my script.  Drop Field can be used in the script to drop one or more fields from the data model.  The syntax is as follows:

To drop field A from all tables in the data model:

Drop Field A;

To drop field A from only table X:

Drop Field A From X;

To drop more than one field (A and B) from the data model:

Drop Fields A, B;

To drop more than one field (A and B) from multiple tables (X, Y):

Drop Fields A, B From X, Y;

I often use Drop Fields when I am creating a link table in my data model.  You can read more about link tables in Arturo’s Concatenate vs Link Table blog.  When I have more than one table linked by more than one field in my data model, I create a link table to eliminate the synthetic table and link the tables by just one field.  To do this, I first create a composite key in each of my tables.  Then I create a link table that stores the composite key and the fields that make up the composite key.  To avoid creating another synthetic table, I drop the fields that make up my composite key from the original tables.  Using some of Arturo’s example in his technical brief, here is an example of the script:

full script.png

In the script above, I used Drop Fields to drop the Year, EmployeeID and ProductID from the Sales and Budget tables.  Since these fields are now in the link table, I no longer needed them in the Sales and Budget tables.

Drop Table works in a similar way.  Drop table can be used in the script to drop one or more tables from the data model.  The syntax is as follows:

To drop table X:

Drop Table X;

To drop more than one table (X and Y):

Drop Tables X, Y;

Often in my script I create a new table based on an existing table.  When I do this, I frequently use Drop Table to delete the original table to prevent synthetic tables.  In the example script below, the Drop Table statement deletes the SalesTemp table and all its fields from the data model.  Therefore, the SalesTemp table can no longer be used in the script after the Drop Table statement.

script drop.png

Drop Field and Drop Table are useful scripting statements that help you create a data model that is free of synthetic tables and unnecessary fields and tables.  So the next time you find a synthetic table in your data model, check to make sure you did not forget to drop a field or a table.

Thanks,

Jennell

5 Comments
benrig44
New Contributor III

‌one thing to keep in mind about DROP TABLE or DROP TABLES (certainly in V11, I can't speak beyond that) is that if you wish to drop more then one table at the same time it can be faster to issue a single [DROP TABLES table1, table2, table3] command than a separate DROP TABLE command for each table. In several tests I did on large data models with many tables it was 10's of minutes faster

this is especially noticeable at the end of a script where you want to drop all tables from memory

i I believe the reason is that after QVW issues each DROP TABLE or DROP TABLES command  QVW must reassess the data model including Bit Stuffed Pointers which can take some time. By issuing a single command instead of several you reduce the number of times QVW has to perform that analysis.

1,663 Views
Not applicable

Very interesting point Steven.  I believe the same may be true for Drop Field vs Drop Fields (at least in the negative impact to performance of splitting the statements on larger data models - I have not tried the fix yet).  Thanks for the sharing - I hadn't really considered this before, but it makes total sense in terms of the way QV would need to perform the actions, so bundling them into one statement, while less readable, should improve performance.

1,663 Views
Not applicable

Yes - appears to be true for drop field also.  I created a simple test (script below if you want to recreate).

It took about 5-6 seconds to drop all 19 fields individually, but only 1 second to do them all in one drop field statement.


Something to be aware of!

Logs From Test for Individual Statements:

22/12/2016 14:35:39: 0063  DROP FIELD Expression1

22/12/2016 14:35:39: 0064  DROP FIELD Expression2

22/12/2016 14:35:39: 0065  DROP FIELD Expression3

22/12/2016 14:35:40: 0066  DROP FIELD Expression4

22/12/2016 14:35:40: 0067  DROP FIELD Expression5

22/12/2016 14:35:41: 0068  DROP FIELD Expression6

22/12/2016 14:35:41: 0069  DROP FIELD Expression7

22/12/2016 14:35:41: 0070  DROP FIELD Expression8

22/12/2016 14:35:42: 0071  DROP FIELD Expression9

22/12/2016 14:35:42: 0072  DROP FIELD Expression10

22/12/2016 14:35:42: 0073  DROP FIELD Expression11

22/12/2016 14:35:43: 0074  DROP FIELD Expression12

22/12/2016 14:35:43: 0075  DROP FIELD Expression13

22/12/2016 14:35:43: 0076  DROP FIELD Expression14

22/12/2016 14:35:43: 0077  DROP FIELD Expression15

22/12/2016 14:35:44: 0078  DROP FIELD Expression16

22/12/2016 14:35:44: 0079  DROP FIELD Expression17

22/12/2016 14:35:44: 0080  DROP FIELD Expression18

22/12/2016 14:35:44: 0081  DROP FIELD Expression19

22/12/2016 14:35:45:      Execution finished.

Logs From Test for Single Statement:

22/12/2016 14:39:13: 0085  DROP FIELDS

22/12/2016 14:39:13: 0086   Expression1

22/12/2016 14:39:13: 0087   ,Expression2

22/12/2016 14:39:13: 0088   ,Expression3

22/12/2016 14:39:13: 0089   ,Expression4

22/12/2016 14:39:13: 0090   ,Expression5

22/12/2016 14:39:13: 0091   ,Expression6

22/12/2016 14:39:13: 0092   ,Expression7

22/12/2016 14:39:13: 0093   ,Expression8

22/12/2016 14:39:13: 0094   ,Expression9

22/12/2016 14:39:13: 0095   ,Expression10

22/12/2016 14:39:13: 0096   ,Expression11

22/12/2016 14:39:13: 0097   ,Expression12

22/12/2016 14:39:13: 0098   ,Expression13

22/12/2016 14:39:13: 0099   ,Expression14

22/12/2016 14:39:13: 0100   ,Expression15

22/12/2016 14:39:13: 0101   ,Expression16

22/12/2016 14:39:13: 0102   ,Expression17

22/12/2016 14:39:13: 0103   ,Expression18

22/12/2016 14:39:13: 0104   ,Expression19

22/12/2016 14:39:13: 0105 

22/12/2016 14:39:14:      Execution finished.

Script Used:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Characters:

Load Chr(RecNo()+Ord('A')-1) as Alpha, RecNo() as Num autogenerate 26;

ASCII:

Load

if(RecNo()>=65 and RecNo()<=90,RecNo()-64) as Num,

Chr(RecNo()) as AsciiAlpha,

RecNo() as AsciiNum

autogenerate 255

Where (RecNo()>=32 and RecNo()<=126) or RecNo()>=160 ;

Transactions:

Load

TransLineID,

TransID,

mod(TransID,26)+1 as Num,

Pick(Ceil(3*Rand1),'A','B','C') as Dim1,

Pick(Ceil(6*Rand1),'a','b','c','d','e','f') as Dim2,

Pick(Ceil(3*Rand()),'X','Y','Z') as Dim3,

Round(1000*Rand()*Rand()*Rand1) as Expression1,

Round(  10*Rand()*Rand()*Rand1) as Expression2,

Round(Rand()*Rand1,0.00001) as Expression3,

Round(1000*Rand()*Rand()*Rand1) as Expression4,

Round(  10*Rand()*Rand()*Rand1) as Expression5,

Round(Rand()*Rand1,0.00001) as Expression6,

Round(1000*Rand()*Rand()*Rand1) as Expression7,

Round(  10*Rand()*Rand()*Rand1) as Expression8,

Round(Rand()*Rand1,0.00001) as Expression9,

Round(Rand()*Rand1,0.00001) as Expression10,

Round(1000*Rand()*Rand()*Rand1) as Expression11,

Round(  10*Rand()*Rand()*Rand1) as Expression12,

Round(Rand()*Rand1,0.00001) as Expression13,

Round(1000*Rand()*Rand()*Rand1) as Expression14,

Round(  10*Rand()*Rand()*Rand1) as Expression15,

Round(Rand()*Rand1,0.00001) as Expression16,

Round(1000*Rand()*Rand()*Rand1) as Expression17,

Round(  10*Rand()*Rand()*Rand1) as Expression18,

Round(Rand()*Rand1,0.00001) as Expression19

;

Load

Rand() as Rand1,

IterNo() as TransLineID,

RecNo() as TransID

Autogenerate 10000000

While Rand()<=0.5 or IterNo()=1;

Comment Field Dim1 With "This is a field comment";

//DROP FIELD Expression1;

//DROP FIELD Expression2;

//DROP FIELD Expression3;

//DROP FIELD Expression4;

//DROP FIELD Expression5;

//DROP FIELD Expression6;

//DROP FIELD Expression7;

//DROP FIELD Expression8;

//DROP FIELD Expression9;

//DROP FIELD Expression10;

//DROP FIELD Expression11;

//DROP FIELD Expression12;

//DROP FIELD Expression13;

//DROP FIELD Expression14;

//DROP FIELD Expression15;

//DROP FIELD Expression16;

//DROP FIELD Expression17;

//DROP FIELD Expression18;

//DROP FIELD Expression19;

DROP FIELDS

  Expression1

  ,Expression2

  ,Expression3

  ,Expression4

  ,Expression5

  ,Expression6

  ,Expression7

  ,Expression8

  ,Expression9

  ,Expression10

  ,Expression11

  ,Expression12

  ,Expression13

  ,Expression14

  ,Expression15

  ,Expression16

  ,Expression17

  ,Expression18

  ,Expression19

  ;

0 Likes
1,663 Views
benrig44
New Contributor III

‌good to know. Once I get to a real computer (and not my iPad) i will share the SUB procedure I created that will generate the necessary compound DROP TABLES command either given a list of tables or a wildcard to drop all tables at once. I will create an equivalent one for DROP FIELDS as well.

0 Likes
1,663 Views
mastankomatla21
New Contributor III

Hi Team,

Please find below scenario on section access.

I have a Region field ,needs to apply data level security on Region field.

Region field having 100 regions.

For User A should show 100 regions

       User B should show 40 regions

     User C should show 60 regions

 

How can i achieve this?

0 Likes
1,275 Views