Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld happening right now! You can still join: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
datanibbler
Champion
Champion

How to: Easily get the list of fields of a database table

Hi,

quite regularly you have a LOAD statement with an * which is a bit unpractical and you want a list of all the fields in the table.

(in my instance, I had to construct a new LOAD and I had to do some guessing because I cannot execute LOADs from the database locally, but only via the QMC, so I cannot actually see what fields are in the database_table, so I had to use the *)

In that case, the easiest way to get a list of the fields that you can copy into the script (you cannot copy from the table_viewer) is this:

=> Build a FOR NEXT loop to load into some table the name of all the fields in the table:

   >> LET v_nr_fields = NoOfFields();

          FOR i = 1 TO $(v_nr_fields)

              Temp: FIRST 1 LOAD Fieldname($(i), '[tablename]') as Feldname RESIDENT [tablename];

          NEXT

   <<

Then open the table_viewer where you can view this table and use the "export structure" feature.

=> Open the >Fields.tab< file in Excel

=> Voila, you can export the list of fields and using an Excel_formula, you can easily add the >comma<

Best regards,

DataNibbler

3 Replies
m_woolf
Master II
Master II

If you have loaded a table with *, you can make a list box that shows the system field $Table and a list box that shows $Field.

Then selecting any Table in the first list box will show all the available fields in the second list box.

colinodonnel
Creator
Creator

Yes that loop works nicely.

 

Had to make a couple of small changes to the script as the table viewer wouldn't export for me.

(note that the brackets are removed from the table name in the formula Fieldname($(i), 'RawData')    )

RawData:

Load  * from QVD;

 

   LET v_nr_fields = NoOfFields('RawData');

          FOR i = 1 TO $(v_nr_fields)

              Temp: FIRST 1 LOAD Fieldname($(i), 'RawData') as Fieldname RESIDENT [RawData];

          NEXT

Drop table RawData;

Store * from Temp into [lib/temp.csv] (txt);

 

 

 

 

 

colinodonnel
Creator
Creator

works perfectly