Creating Procedures in Qlikview

[sgg_gaikwad]

I have Created this procedure for Qvd Creation & Dropping original table but Dropping table function is not working can anyone give solution on it?

FOR  i = 1 to NoOfTables();

let vtable=TableName($(i)-1);

let vname='$(vtable).qvd';

store $(vtable) into $(vtable).(qvd);

//DROP tables $(vtable) ;

NEXT  i ;

[#2]

I think that different things will NOT work:

1) DROP table $(vtable);

2) Because you drop table(s) into a loop, take care of index

TableName(counter) if the counter is incrementing and your tables decrementing, you will miss some tables

Same (but less sure) about NoOfTables() : I do not know if the number is evaluated once at the beginning or each time. In the second case, you need to store the number in a local variable before the loop.

Fabrice

[Anonymous]

Not quite sure if you want to drop all tables but if so then this is what I use.

let numTables = NoOfTables();
for i=1 to $(numTables)
let tt = TableName(0);
drop table [$(tt)];
next

[sgg_gaikwad]

Hi Nils,

     It's Working but i need Single Procedure for Creating QVD & Dropping tables..?

[Michiel_QV_Fan]

I have created this statement:

//Load tables from SQL

for  tabellen = 0 to NoOfRows('Database_tabellen') -1

let vDatabase_tabelnaam = peek('Table',tabellen,'Database_tabellen');

let vToQVDnaam = peek('QVD_naam',tabellen,'Database_tabellen');

set tablename = $(vToQVDnaam); //set variable for all additionale statements and logging

$(vToQVDnaam):

LOAD *;

SQL SELECT *

FROM $(databasename).$(databaseowner)."$(database_schema)$(vDatabase_tabelnaam)";

call store_routine;

TRACE ---------------------------------------------------------------------;

next tabellen

Where the store routine is called:

sub store_routine

store $(tablename) into QVD\$(tablename).qvd (qvd);

drop table $(tablename);

set tablename = ;

end sub

I load from an external txt file the table name and give it a QVD name, without spaces. You can do that inline also.

The main difference is that I use Peek to extract the next table(name).

[giakoum]

Just change $(i)-1 to 0 :

FOR  i = 1 to NoOfTables();

     let vtable=TableName(0);

     let vname='$(vtable).qvd';

     store $(vtable) into $(vtable).(qvd);

     DROP tables $(vtable) ;

NEXT  i ;

[#7]

Ioannis,

One question about the loop.

The numbers of loops is evaluated at the enter of the loop (as in VBA for ex) ? NoOfTables will change during the loop but the number of iteration will remain the same ?

Cheers

Fabrice

[giakoum]

yes, number of iterations will remain the same.

it is just that when the 1st table is dropped, the remaining ones go one position back. So for example, if you have 2 tables, when you drop the first (tableName(0)), then the second will become first (tableName(1) will then be tableName(0)). That is why tableName(0) is dropped in every iteration.

[sgg_gaikwad]

Hi Loannis,

                 Thanx for the answer its working now..

Could you please tell me Can i Use subroutine within this Script..?

[#10]

Yes you can use subroutines into the script

Just, you need to write the code BEFORE the call of it (for example a tab before that groups all the sub routines)

Fabrice