Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

2 Replies

Re: Reading loaded FieldNames from QV Table.

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.
Not applicable

Re: Reading loaded FieldNames from QV Table.

Thanks a lot for your input Bill !!!

I guess, system table will return only the QV table name. Is there any option for getting DB source table and columns?

Meanwhile I'm just analyzing the feasibility by using system tables to accomplish  this. This is a great idea in need.

Community Browser