Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Check if table exist

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

7 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

-Rob

Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

-Rob

Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

-Rob

Not applicable
Author

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
Not applicable
Author

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