You are here

Cleaning up Temp Tables and Fields in QlikView and Qlik Sense.

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.

QlikView south africa

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=;
//*********************************************************************************