5 Replies Latest reply: Apr 8, 2015 4:30 PM by Beaulieu Mathieu RSS

    Create table dynamically with SUB

    Beaulieu Mathieu

      Hi everybody,

      I want to create many tables dynamically.

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

      TableNameQvdFileAlias
      TableSTTQVD_STT.QVD
      TableOSERQVD_OSER_01.QVDAlias 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

      [D:\QlikView\Application\Params\Param_Load.xlsx]

      (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

      [D:\QlikView\Application\Params\Param_Load.xlsx]

      (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