topic Re: Replace loop table in QlikView

Replace loop table

ivandrago — Fri, 24 Feb 2017 23:09:07 GMT

Hi,

I have the below script which loops through a list of sql tables that I have listed on the I previous sheet script and stores this into a QVD. Some of the table names I want to replace a finding word with an empty text, but I am getting a error when I tried the below amendment, any ideas?

//Current script

LET vDataStoreDir = '..\02_QVD\';

FOR i = 0 TO NoOfTables() - 1

LET d = TableName(i);

STORE '$(d)' INTO '$(vDataStoreDir)'S1-'$(d).qvd';

LET j = nooftables();

DO WHILE j > 0

LET d = TableName(0);

DROP TABLE '$(d)';

LET j = nooftables();

LOOP

//Amended script

LET vDataStoreDir = '..\02_QVD\';

FOR i = 0 TO NoOfTables() - 1

LET d = TableName(i);

STORE replace('$(d)','fact','') INTO '$(vDataStoreDir)'S1-'$(d).qvd';

LET j = nooftables();

DO WHILE j > 0

LET d = TableName(0);

DROP TABLE '$(d)';

LET j = nooftables();

LOOP

Re: Replace loop table

Mon, 27 Feb 2017 20:30:59 GMT

Don't think you can use a function directly as the table argument to STORE.

I would suggest changing the 'LET d =' instead of the STORE:

LET d = TableName(i);

LET t = replace(d,'fact','');

STORE '$(t)' INTO '$(vDataStoreDir)'S1-'$(d).qvd';

The latter can perhaps be written as follows for clarity:

STORE [$(t)] INTO [$(vDataStoreDir)S1-$(d).qvd];

Re: Replace loop table

marcus_sommer — Tue, 28 Feb 2017 08:41:48 GMT

Must it not be:

STORE '$(d)' INTO '$(vDataStoreDir)'S1-'$(t).qvd';

Another approach could be the following:

FOR i = 0 TO NoOfTables() - 1

LET source = TableName(i);

LET target = replace(TableName(i),'fact','');

Rename table $(source) to $(target);

STORE '$(target)' INTO '$(vDataStoreDir)'S1-'$(target).qvd';

- Marcus