Qlik Community

QlikView Documents

Documents for QlikView related information.

Script for Storing/ Dropping Table with Exceptional list

balar025
Contributor III

Script for Storing/ Dropping Table with Exceptional list

Problem Statement:

Always tedious task to maintain drop table statement for all existing table of any Application.

Below script handle table list which should be there even if there is drop statement. At the end of script it will create dropped table list to track it.

For Example;

// Table T1
T1:
Load
RowNo() as ID,
RowNo()*RowNo() as MeasureT1
AutoGenerate(24);

// Table T2
T2:
Load
RowNo() as ID,
RowNo()*RowNo() as MeasureT2
AutoGenerate(24);

// Table T3
T3:
Load
RowNo() as ID,
RowNo()*RowNo() as MeasureT3
AutoGenerate(24);

Below is the script for creating exception list. You can add your own exception list.

//List of Exceptional Data table which should be omitted from table storing/dropping script
TableExceptionList:
Load
Concat(chr(39)&ExceptionList&chr(39),',') as ExceptionList;
Load * Inline
[
ExceptionList
TableExceptionList
TableDetails
T1
]
;

Let vExceptionList = Peek('ExceptionList',0,'TableExceptionList');



Get the No Of Tables from the application


// No of Table in Applications
Let vNoOfTables = NoOfTables();
Trace $(vNoOfTables);

//Removal of Table
Let i = $(vNoOfTables);
do while i > 0
Let i = $(i)  - 1 ;
Let vTableName = TableName($(i));

//Check in Exception List
     vtableException = WildMatch('$(vTableName)',$(vExceptionList));
if $(vtableException) = 0 then
//Make Entry of Store/Dropped Table
                TableDetails:
Load '$(i)' as TableId, '$(vTableName)' as Table autogenerate 1;              

//Storing a table into QVD
                Store $(vTableName) into $(vQVDPath)\$(vTableName).qvd(qvd);
//Dropping a table
                Drop table $(vTableName)

Trace Stored & Dropped Table $(vTableName);
ENDIF
loop;

Always Welcome, if any suggestion.

Comments
santiago_respane
Valued Contributor

Very interesting and usefull, thanks for sharing.

Kind regards,

balar025
Contributor III

Thanks Santiago. I think Storing/Droping of table is common part of every qlikview development. So, better to have smart way to handle that rather than doing all manual.

stantrolav
Contributor II

Too long script. Here is shorter:

Binary [lib://AttachedFiles/SPIK_Strogiy_mod_20_EXCEL.qvw];

//Всего таблиц для обработки

scriptNoOfTables = NoOfTables();

Trace $(scriptNoOfTables);

//Обрабатываю по отдельности каждую таблицу

For i = 1 to $(scriptNoOfTables)

scriptTableName = TableName($(i));

Trace $(scriptTableName);

        if '$(scriptTableName)' <> '' then

Store [$(scriptTableName)] into [lib://SourceFiles/PackageData\$(scriptTableName).qvd] (qvd);

EndIf;

   

Next i;

balar025
Contributor III

Hello Stanislav,

Yes you are right but I have included exception list which contains table name which we want to retain in data model. And Script looks long due to comment.

Bellow is the main script.

// No of Table in Applications
Let vNoOfTables = NoOfTables();
Trace $(vNoOfTables);

//Removal of Table
Let i = $(vNoOfTables);
do while i > 0
Let i = $(i)  - 1 ;
Let vTableName = TableName($(i));

//Check in Exception List
     vtableException = WildMatch('$(vTableName)',$(vExceptionList));
if $(vtableException) = 0 then
//Make Entry of Store/Dropped Table
                TableDetails:
Load '$(i)' as TableId, '$(vTableName)' as Table autogenerate 1;              

//Storing a table into QVD
                Store $(vTableName) into $(vQVDPath)\$(vTableName).qvd(qvd);
//Dropping a table
                Drop table $(vTableName)

Trace Stored & Dropped Table $(vTableName);
ENDIF
loop;


Thanks

Ravi Balar

Version history
Revision #:
1 of 1
Last update:
‎08-04-2017 05:50 AM
Updated by: