topic Re: Creating and Dropping temp tables in QlikView

Creating and Dropping temp tables

Anonymous — Fri, 06 Feb 2015 19:49:07 GMT

Hello, Qlikview group. I have been an acive reader of this community but just signed up as I am hoping for help with SQL to Qlikview script. I have taken a developer/designer class several months ago but until now have not applied the knowledge until now...

Question:

I wrote a SQL query and tried to copy and paste this into Qlikview but I am getting an error. Can someone take a look at the script and tell me where I went wrong and maybe give me some pointer for the future, thanks...

Hopefully I am somewhat close

ODBC

CONNECT32 TO xxxxxx
SQL
TEMPTEST:
USE AA
SELECT TOP 1000

[AA]
       ,[mktId]
           ,[effDt]
       ,[termDt]
       ,[termReasonCd]
       ,[uniqueNbr]

FROM [Ntwk];

SQL
TEMPTEST2:
USE AA
SELECT TOP 1000

[AA],

[BB]

FROM [NtwkMst] ;

SQL
USE AA
SELECT TOP 1000      [effDt]
       ,[termDt]
       ,[termReasonCd]
       ,[AA]

,[BB]

FROM TEMPTEST2
INNER JOIN TEMPTEST ON TEMPTEST2.AA = TEMPTEST.AA;

DROP TABLE TEMPTEST;
DROP TABLE TEMPTEST2

Re: Creating and Dropping temp tables

maxgro — Fri, 06 Feb 2015 20:01:12 GMT

and the error is?

try this (remove use..., if needed put the db before the table, move tempest)

and let we know

ODBC CONNECT32 TO xxxxxx;

TEMPTEST:

SQL
SELECT TOP 1000

[AA]
       ,[mktId]
           ,[effDt]
       ,[termDt]
       ,[termReasonCd]
       ,[uniqueNbr]
FROM [Ntwk];

Re: Creating and Dropping temp tables

Anonymous — Fri, 06 Feb 2015 20:08:13 GMT

modify your script to look as written below

ODBC CONNECT32 TO xxxxxx

TEMPTEST:

SQL

//USE AA

SELECT TOP 1000

[AA]

,[mktId]

,[effDt]

,[termDt]

,[termReasonCd]

,[uniqueNbr]

FROM [Ntwk];

inner join(TEMPTEST)

TEMPTEST2:

SQL

//USE AA

SELECT TOP 1000

[AA],

[BB]

FROM [NtwkMst];

//DROP TABLE TEMPTEST;

//DROP TABLE TEMPTEST2;

DROP FIELDS [mktId], [uniqueNbr];

Re: Creating and Dropping temp tables

Anonymous — Fri, 06 Feb 2015 20:25:40 GMT

@autopilot - Your solution worked, Now it's about understamding the difference between Qlikview and SQl syntax. Thanks!

Re: Creating and Dropping temp tables

Anonymous — Fri, 06 Feb 2015 20:30:29 GMT

My previous comment is the most straight-forward solution based on what you provided...however, below will provide some explanation for how to perform the load from temp table approach you originally outlined using qlikview.

You were definitely in the right direction from a logic perspective....just a few areas related to syntax were off.

If you load data in qlikview using the LOAD or SQL Select statements the table resides in the qlikview app memory. To access the table later in the script (for any number of potential reasons), you must use

LOAD...RESIDENT <tablename>;

Here's an example.

// use load statement to create temp table 1 from source qlikview data file (i.e. qvd)

TempTable1:

LOAD

field1,

field2,

field3

from source.qvd(qvd);

// use sql select statement to create temp table 2 and then

// left join to temp table 1 on TempTable1.field1 = TempTable2.sql_field1 by aliasing the field names to be the same

left join (TempTable1)

TempTable2:

SQL Select

sql_field1 as field1,

sql_field2,

sql_field3

from dbo.TempTable;

// use load..resident statement to reload temp table 1 as a new table called Table1

// also alias field name to illustrate simple example of reloading an existing "temp" table

Table1:

LOAD

field1 as Field1,

field2 as Field2,

field3 as Field3,

sql_field2 as SQL_Field2,

sql_field3 as SQL_Field3

RESIDENT

TempTable1;

// remove temp table 1 from qlikview memory as it is no longer needed

drop table TempTable1;

Re: Creating and Dropping temp tables

Anonymous — Fri, 06 Feb 2015 20:34:59 GMT

I wanted to provide the solution and then take time to write the explanation....which I added in a reply a minute ago.

Let me know if that doesn't make sense. For whatever reason I can't seem to get the community editor to paste the text with the colors for load script so you may want to copy paste into a qv app for better readability.