Check if table exist

[#1]

Hello,
I've created a temporary table, TempTable, and can you please tell me the syntax if the table exists so I can drop it before
creating a new one?

Can someone please show me the syntax on how to check if the table exists?
if exists('temptable)
drop table temptable;
end if

[rwunderlich]

IF NoOfRows('temptable') > 0 THEN
DROP TABLE temptable;
END IF

-Rob

[#3]

Hi Rob

Thanks for the quick response but when I put the code, I get the SCRIPT LINE ERROR of

"IF NoOfRows('temptable') > 0'

Thanks,

Reyn

[rwunderlich]

Can you post the entire script block without adding any quotes? It should work. What version QV?

-Rob

[#5]

Rob,
Thank you very much for your help on this matter. MY QlikView is version 9 and here's my script.

Let varMinDate = num(Peek('OrderDate',0,'Orders'));
Let varMaxDate = num(Peek('OrderDate',-1,'Orders'));
Let varToday = num(today());

IF NoOfRows('temptable') > 0 THEN
DROP TABLE temptable;
END IF

temptable:
Load
$(varMinDate) + rowno() - 1 as num
, date($(varMinDate) + rowno() -1) as TempDate
AUTOGENERATE
$(varMaxDate) - $(varMinDate) + 1;

Sincerely,
Reyn

[rwunderlich]

It looks ok to me. Can you post the portion of the document log where the error message appears?

-Rob

[#7]

Hello Rob,

A came across your solution and it worked perfectly except when I had an empty table (no rows).

So another approach is to use the following:

if not isnull(TableNumber('temptable')) then
 drop table temptable;
end if

This will drop temptable if it exists wether it has 0 or more rows.

Best regards

Henrik

[Anonymous]

As a reaction on henke_steen, I've created a subfunction.

You could place your SUBS in a separate tab in your load script or use the include method and place this script in an QVS file.

SUB DROPTABLES(vDropTableName)

// DROPS table if exist in memory.

// Normal operation in Qlikview is to create an error if table not exists or has 0 records

// Input  : tablename

// Usage  : CALL DROPTABLES('Table Name')

// Author : Roland Raijmakers

IF NOT ISNULL(TableNumber(vDropTableName)) THEN

        Let vNoRows=NoOfRows(vDropTableName);

        TRACE DROP EMPTY TABLE: DROP Table '$(vDropTableName)'  CONTAINS: $(vNoRows) Rows ;

        DROP Table '$(vDropTableName)';

ENDIF

END SUB