topic Re: Massive Drop tables in QlikView

Massive Drop tables

ecabanas — Thu, 22 Sep 2016 16:40:11 GMT

Hi all,

I have a process that load several xlsx file every night and put all the info into a QVD. The problem is how to delete all tables after the loading.

LOAD [Article Code]as ItemId,

Upper(Proveedor) as Proveedor_Tarifa,

Tarifa,

Fecha as From

FROM (ooxml, embedded labels, table is Tarifas);

NoConcatenate

QVD:

Load ItemId, Tarifa, Upper(Proveedor_Tarifa) as Proveedor_Tarifa,From,Proveedor_Tarifa&'-'&ItemId&'-'&From as Tarifa_Id

FROM (qvd);

Concatenate (QVD)

load ItemId, Tarifa, upper(Proveedor_Tarifa) as Proveedor_Tarifa,From,ItemId&'-'&From as Tarifa_Id resident Tarifas_New;

drop table Tarifas_New%;

Many thank's

Eduard

Re: Massive Drop tables

Gysbert_Wassenaar — Thu, 22 Sep 2016 16:51:18 GMT

List each table by its name:

DROP TABLE Tarifas, Tarifas_New;

If you have dozens of tables you may want to use a loop:

For i =1 to NoOfTables()-1

LET vTable = TableName($(i))

When Match( '$(vTable)' , 'Tarifas') DROP TABLE $(vTable) ;

Re: Massive Drop tables

sunny_talwar — Thu, 22 Sep 2016 16:55:17 GMT

Is this not working?

Tarifas_New:

LOAD [Article Code] as ItemId,

Upper(Proveedor) as Proveedor_Tarifa,

Tarifa,

Fecha as From

FROM

(ooxml, embedded labels, table is Tarifas);

QVD:

NoConcatenate

Load ItemId,

Tarifa,

Upper(Proveedor_Tarifa) as Proveedor_Tarifa,

From,

Proveedor_Tarifa&'-'&ItemId&'-'&From as Tarifa_Id

FROM (qvd);

Concatenate (QVD)

LOAD ItemId,

Tarifa,

Proveedor_Tarifa,

From,

Proveedor_Tarifa&'-'&ItemId&'-'&From as Tarifa_Id

Resident Tarifas_New;

DROP Table Tarifas_New;

Re: Massive Drop tables

ecabanas — Thu, 22 Sep 2016 16:55:29 GMT

The problem is I don't know the name, it load for example 3 tables one for each excel loaded.

Tarifas_New1

Tarifas_New2

Tarifas_New3

till 'N' tables, depending on the number of excel sheet loaded..

Re: Massive Drop tables

Gysbert_Wassenaar — Thu, 22 Sep 2016 16:57:57 GMT

Then you need to check that your excel files contain exactly the same number of fields with exactly the same names. If they don't then each excel file will result in a new table.

Re: Massive Drop tables

marcus_sommer — Fri, 23 Sep 2016 01:16:12 GMT

Maybe it's easier and more flexible to load multiple files with a filelist-statement:

Loop through Folders and sub folders to get file names

Dynamically Loading Multiple Excel Files

- Marcus