topic Creating Procedures in Qlikview in QlikView

Creating Procedures in Qlikview

sgg_gaikwad — Tue, 11 Feb 2014 12:17:35 GMT

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 ;

Re: Creating Procedures in Qlikview

Tue, 11 Feb 2014 12:23:51 GMT

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

Re: Creating Procedures in Qlikview

Anonymous — Tue, 11 Feb 2014 12:54:17 GMT

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

Re: Creating Procedures in Qlikview

sgg_gaikwad — Tue, 11 Feb 2014 13:09:57 GMT

Hi Nils,

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

Re: Creating Procedures in Qlikview

Michiel_QV_Fan — Tue, 11 Feb 2014 13:18:39 GMT

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).

Re: Creating Procedures in Qlikview

giakoum — Tue, 11 Feb 2014 13:25:18 GMT

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 ;

Re: Creating Procedures in Qlikview

Tue, 11 Feb 2014 14:20:51 GMT

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

Re: Creating Procedures in Qlikview

giakoum — Tue, 11 Feb 2014 16:03:41 GMT

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.

Re: Creating Procedures in Qlikview

sgg_gaikwad — Wed, 12 Feb 2014 06:36:16 GMT

Hi Loannis,

Thanx for the answer its working now..

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

Re: Creating Procedures in Qlikview

Wed, 12 Feb 2014 08:02:43 GMT

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

Re: Creating Procedures in Qlikview

giakoum — Wed, 12 Feb 2014 08:19:07 GMT

Hi.

I think Fabrice cowered your question. Classic syntax : Sub ... end sub and Call to call it in the script.

Re: Creating Procedures in Qlikview

KumaRan — Thu, 07 Nov 2019 12:16:21 GMT

Hi Sgg_gaikwad

i am experiencing the same issue.Can you post a code for a single subroutine to store and drop qvds?