2 Replies Latest reply: Mar 23, 2015 3:21 AM by Jayavignesh G RSS

    Reading loaded FieldNames from QV Table.

      Dear QV's,

       

      I just need your opinion/idea for the below scenario which I came across in one of my recent development.

      I've loaded the fields from DB and stored into QVD. The script is very simple as below.

       

      EMPLOYEE:

      Load NAME as EMP_NAME,

        ID,

        DEPT as DEPARTMENT;

      SQL select * from EMPLOYEE.INFO;

       

      Store EMPLOYEE into EMPLOYEE.qvd(qvd);

       

      The requirement is to store the meta data(only field names) into separate qvd.

      Currently we're doing this as below.

       

      MetaData:

      LOAD * INLINE [

        SourceField , MetaSourceTable , QVDField ,QVDFieldDescription

        NAME , EMPLOYEE , EMP_NAME ,Employee Name

        ID , EMPLOYEE , ID ,Employee ID

        DEPT , EMPLOYEE , DEPARTMENT ,Department of the Employee

        ];

      Call StoreQVDMetaData;

       

      'StoreQVDMetaData' is the sub Routine(qvs file) in which we have scripts to write the Meta table information into QVD.


      The problem here is each time I need to manually hardcode the loaded fields& Tables into my Inline table which is bit time consuming if I've 20+ fields in my table.

      Could you guys please let me know if there are any functions/procedure I can use to get the loaded fields automatically into my meta data table.I know that I cannot automate Field Description so I thought of removing this from meta data table. The only thing I need is to get fields and table name from already loaded table by having any loop or something.

       

      Kindly post your ideas.

       

      Thanks!

       

      - JAY

        • Re: Reading loaded FieldNames from QV Table.
          Bill Markham

          Would the System Fields be of use ?

           

          Below pasted in from the Help.

           

           

           

          System Fields

          In addition to the fields extracted from the data source, system fields are also produced by QlikView. These all begin with "$" and can be displayed in list boxes much like ordinary fields. System fields are typically created during Script execution, are primarily used as an aid in document design.

          Displaying System Fields

          Perform the following steps:

          1. Right-click on the sheet and select System Fields.
          2. Check the Show System Fields box.

          The system fields are now available as any other fields.

          Available System Fields

          The following system fields can be displayed:

             

          $Table Displays all internal tables loaded by the script. When a single table is selected, an information symbol will activate in the caption area of the list box. By clicking here, it is possible to view the table, if it comes from a file.
          $Field Displays the fields that are read from the tables. By setting this list box to Show Frequency in the List Box Properties: General page, it is simple to detect key fields that occur in several internal tables.
          $Fields The numbers in this list box represent the number of fields in different tables.
          $FieldNo This list box shows the position of the fields in the tables.
          $Rows This list box shows the number of rows in the tables.
          $Info If info tables have been included in the document, their names will be displayed here.

          System Table

          QlikView can automatically create a pivot table that makes use of the system fields.The table is called System Table and contains the two dimensions $Field and $Table and the expression only([$Field]). The system table is sorted according to frequency by default. The system table object is not available for the QlikView Server clients (AJAX and Plugin clients).

          Creating a System Table

          Perform the following steps:

          1. Right-click on the sheet and select New Sheet Object.
          2. Select System Table.