Create table dynamically with SUB

[#1]

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

[#2]

Nobody can help me?

[#3 · Accepted solution]

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

[#4]

Yes, I have just find this solution !

Thanx.

Problem solved.

[pokassov]

Hi, Beaulieu!

You have to change only one line in your script

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

Sergey

[#6]

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