Most of the time we use temporary fields and tables in our load scripts in Qlik.

Usually we have some sort of a naming convention like temp_TableName or TableName_temp.

I quite like the way a temp table is identified in SQL with a #-prefix, so I adopted this naming convention for my temp fields and tables in Qlik.

This brought with it a nice advantage in that all the #-fields and #-tables are listed at the top.

Now you can easily see if you have any temp fields and tables left in the app.

This got me thinking: if all my temp tables and fields follow a strict naming convention, I can identify and drop those at the end of my script easily, and not have to worry about them being left behind.

This little script helps me do that.

//****************Drop #-prefixed Temp Tables and Fields****************************
//Drop all #Tables
for xTableCount = nooftables()-1 to 0 step -1
let xTable = tablename(xTableCount);
if wildmatch(‘$(xTable)’,’#*’) then
drop table “$(xTable)”;
endif
next

//Drop all #Fields
for xTableCount = nooftables()-1 to 0 step -1
let xTable = tablename(xTableCount);
for xFieldCount = 1 to nooffields(‘$(xTable)’)
let xField = fieldname(xFieldCount,’$(xTable)’);
if wildmatch(‘$(xField)’,’#*’) then
drop field “$(xField)”;
endif
next
next
//*********************************************************************************

This works good and well, but what about if I need one of those temp fields or tables for debugging?

Do I make things difficult and rename them during debugging, and then rename them back again afterwards?

That didn’t quite make sense. So, I adapted the script to allow me to define a list of fields and tables that are not to be dropped. This way I can list them easily, and once I am done with the debugging, I remove the items from the list and they are all dropped during the next reload. These lists are just comma-separated in the variables.

//****************Drop #-prefixed Temp Tables and Fields****************************
//Drop all #Tables

set xTablesNotToDrop = ‘#Customers, #Sales’;
for xTableCount = nooftables()-1 to 0 step -1
let xTable = tablename(xTableCount);
if wildmatch(‘$(xTable)’,’#*’) and substringcount (‘$(xTablesNotToDrop)’, ‘$(xTable)’) = 0 then
drop table “$(xTable)”;
endif
next

//Drop all #Fields
set xFieldsNotToDrop = ‘#Join_CustCode’;
for xTableCount = nooftables()-1 to 0 step -1
let xTable = tablename(xTableCount);
for xFieldCount = 1 to nooffields(‘$(xTable)’)
let xField = fieldname(xFieldCount,’$(xTable)’);
if wildmatch(‘$(xField)’,’#*’) and substringcount (‘$(xFieldsNotToDrop)’, ‘$(xField)’) = 0 then
drop field “$(xField)”;
endif
next
next
//*********************************************************************************

Here is a full example script (All #-fields and #-tables are dropped):

#Customers:
load * inline [
CustCode, CustName
1, Captain America
2, Thor
3, Deadpool
4, Batman
5, Ghost Rider
6, Green Arrow
7, Green Lantern
8, Wonder Woman
];

#Sales:
load * inline [
CustomerCode, Item, Qty
1, Vibranium Shield, 1
2, Mjolnir , 1
3, Katana, 2
4, Batarang (5 pc), 20
4, Utility Belt, 1
5, Enchanted Chain, 1
6, Arrows (10 pc), 100
6, Bow, 3
7, Power Ring, 1
8, Lasso of Truth, 1
];

WeaponSales:
load
CustCode,
CustName as [Customer Name],
CustCode as #Join_CustCode
resident #Customers;

left join (WeaponSales)
load
CustomerCode as #Join_CustCode,
Item as Weapon,
Qty
resident #Sales;

//****************Drop #-prefixed Temp Tables and Fields****************************
//Drop all #Tables
set xTablesNotToDrop = ”;
for xTableCount = nooftables()-1 to 0 step -1
let xTable = tablename(xTableCount);
if wildmatch(‘$(xTable)’,’#*’) and substringcount (‘$(xTablesNotToDrop)’, ‘$(xTable)’) = 0 then
drop table “$(xTable)”;
endif
next

//Drop all #Fields
set xFieldsNotToDrop = ”;
for xTableCount = nooftables()-1 to 0 step -1
let xTable = tablename(xTableCount);
for xFieldCount = 1 to nooffields(‘$(xTable)’)
let xField = fieldname(xFieldCount,’$(xTable)’);
if wildmatch(‘$(xField)’,’#*’) and substringcount (‘$(xFieldsNotToDrop)’, ‘$(xField)’) = 0 then
drop field “$(xField)”;
endif
next
next

//Delete Variables
let xTablesNotToDrop=;
let xTableCount=;
let xTable=;
let xFieldsNotToDrop=;
let xFieldCount=;
let xField=;
//*********************************************************************************