topic Create table dynamically with SUB in QlikView

Create table dynamically with SUB

Tue, 07 Apr 2015 12:49:17 GMT

Hi everybody,

I want to create many tables dynamically.

Do do that, I have an XLSX file which contains all params like this :

TableName	QvdFile	Alias
TableSTT	QVD_STT.QVD
TableOSER	QVD_OSER_01.QVD	Alias Key as NewKey

I wrote a script to loop on each records of this table.

This is how the script below works, the "TRACE" shows all values of records in the report (one "trace" line per record) :

//Load the params from xlsx

TableParams:

LOAD TableName,

QvdFile,

Alias

FROM

(ooxml, embedded labels, table is Feuil1);

SUB LoadParams

NumRows=NoOfRows('TableParams');

FOR i=0 to $(NumRows)-1

NameOfTable= Peek('TableName', $(i));

QVDNameFile = Peek('QvdFile', $(i));

AliasVariable= Peek('Alias', $(i));

Trace $(i) and Name of table = $(NameOfTable) QVD Name = $(QVDNameFile );

NEXT;

ENDSUB;

Now, I want to load my table like this (new code in bold) :

//Load the params from xlsx

TableParams:

LOAD TableName,

QvdFile,

Alias

FROM

(ooxml, embedded labels, table is Feuil1);

SUB LoadParams

NumRows=NoOfRows('TableParams');

FOR i=0 to $(NumRows)-1

NameOfTable= Peek('TableName', $(i));

QVDNameFile = Peek('QvdFile', $(i));

AliasVariable= Peek('Alias', $(i));

Trace $(i) and Name of table = $(NameOfTable) QVD Name = $(QVDNameFile );

If Len($(AliasVariable))>0 Then

$(AliasVariable);

ENDIF;

$(NameOfTable):

Load *

From QVD\$(QVDNameFile);

NEXT;

ENDSUB;

My problem is that this script works only for the first line of the XLSX.

After the first load, the Trace Message show empty values.

Could you help me?

Thanx

Re: Create table dynamically with SUB

Wed, 08 Apr 2015 12:02:23 GMT

Nobody can help me?

Re: Create table dynamically with SUB

Wed, 08 Apr 2015 13:01:30 GMT

Hi Beaulieu,

I think a possible solution could be something like:

//SUB CODE

SUB LoadParams
  NumRows=NoOfRows('TableParams');

    FOR i=0 to $(NumRows)-1
        NameOfTable= Peek('TableName', $(i),'TableParams');
        QVDNameFile = Peek('QvdFile', $(i),'TableParams');
        AliasVariable= Peek('Alias', $(i),'TableParams');

        Trace $(i) and Name of table = '$(NameOfTable)' QVD Name = '$(QVDNameFile)';
     
      If Len('$(AliasVariable)')>0 Then
          TRACE  '$(AliasVariable)';
      ENDIF;
     
        '$(NameOfTable)':
      Load *
      From QVD\$(QVDNameFile)(qvd);
     
    NEXT;

ENDSUB;

//Load the params from xlsx

TableParams:

LOAD TableName,

QvdFile,

Alias

FROM

Test.xlsx

(ooxml, embedded labels, table is Plan1);

// Call Sub

CALL LoadParams;

Best Regards.

Pablo

Re: Create table dynamically with SUB

Wed, 08 Apr 2015 13:18:56 GMT

Yes, I have just find this solution !

Thanx.

Problem solved.

Re: Create table dynamically with SUB

pokassov — Wed, 08 Apr 2015 13:24:37 GMT

Hi, Beaulieu!

You have to change only one line in your script

If Len('$(AliasVariable)')>0 Then

Sergey

Re: Create table dynamically with SUB

Wed, 08 Apr 2015 20:30:50 GMT

This solution works but i don't understand why the Alias doesn't work.