topic Check if table exist in QlikView

Check if table exist

Thu, 25 Feb 2010 00:23:39 GMT

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

Check if table exist

rwunderlich — Thu, 25 Feb 2010 00:53:57 GMT

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

-Rob

Check if table exist

Thu, 25 Feb 2010 01:27:52 GMT

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

Check if table exist

rwunderlich — Thu, 25 Feb 2010 03:41:09 GMT

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

-Rob

Check if table exist

Thu, 25 Feb 2010 17:59:52 GMT

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

Check if table exist

rwunderlich — Fri, 26 Feb 2010 01:13:01 GMT

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

-Rob

Check if table exist

Mon, 08 Mar 2010 09:53:07 GMT

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

Re: Check if table exist

Anonymous — Thu, 11 Oct 2018 08:58:04 GMT

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