topic Re: Reading loaded FieldNames from QV Table. in QlikView

Reading loaded FieldNames from QV Table.

Mon, 16 Mar 2015 06:03:50 GMT

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.

Anonymous — Mon, 16 Mar 2015 20:22:09 GMT

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:

Right-click on the sheet and select System Fields.
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:

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

Re: Reading loaded FieldNames from QV Table.

Mon, 23 Mar 2015 07:21:55 GMT

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.