topic Re: Creating temporary tables in QlikView

Creating temporary tables

Marcio_Campestrini — Mon, 17 Feb 2014 13:27:29 GMT

Hi!

I have this part of my load script. I'm trying to create multiple QVD files based on a SQL query. Everything seems to work, but the commands inside the FOR...NEXT loop. QV doesn't create the table "NotaFiscal", but stores at NTF table.

NTF:

Select NRNFSC,
       SRNOTA,
       to_date(DTEMIS, 'yyyymmdd') DTEMIS,
       to_date(DTSAID, 'yyyymmdd') DTSAID From rch0090
Where to_date(dtemis, 'yyyymmdd') >= to_date('01-jan-$(minYearOfLoadingBills)');

FOR varYear = $(minYearOfLoadingBills) TO year(today())
   NotaFiscal:
   LOAD *
resident NTF WHERE Year(DTEMIS) = $(varYear);
   STORE     NotaFiscal INTO $(Root)\qvd\rch0090_$(varYear).qvd (qvd);
NEXT;
Drop table NotaFiscal;
Drop table NTF;

Variable minYearOfLoadingBills has value = 2013.

Anyone can help me?

Re: Creating temporary tables

Mon, 17 Feb 2014 13:33:08 GMT

Since both tables essentially are loading the same information, auto concatenation is taking place and thus assuming the first table alias

Write this in your loop statement

NotaFiscal:

NOCONCATENATE

Load *

Next;

Re: Creating temporary tables

maxgro — Mon, 17 Feb 2014 13:38:17 GMT

and also drop table NoteFiscal inside the loop

NTF:

FOR varYear = $(minYearOfLoadingBills) TO year(today())
NotaFiscal:
BOCONCATENATE

LOAD *
resident NTF WHERE Year(DTEMIS) = $(varYear);
STORE NotaFiscal INTO $(Root)\qvd\rch0090_$(varYear).qvd (qvd);

Drop table NotaFiscal;

NEXT;
Drop table NTF;

Re: Creating temporary tables

Marcio_Campestrini — Mon, 17 Feb 2014 13:38:35 GMT

Thanks Byron. It worked just fine!

Re: Creating temporary tables

Mon, 17 Feb 2014 13:58:10 GMT

ooh yes, forgot about the drop table Thanks Massimo