topic Fast way to get all table and field names from an app in App Development

Fast way to get all table and field names from an app

Mon, 20 Jun 2016 11:47:41 GMT

Dear all,

I'd like to have all tables and their fields from an app, there is this piece of code from the Qlik Sense manual (2.2):

//Iterate through the loaded tables

For t = 0 to NoOfTables() - 1

//Iterate through the fields of table

For f = 1 to NoOfFields(TableName($(t)))

Tables:

Load

TableName($(t)) as Table,

TableNumber(TableName($(t))) as TableNo,

NoOfRows(TableName($(t))) as TableRows,

FieldName($(f),TableName($(t))) as Field,

FieldNumber(FieldName($(f),TableName($(t))),TableName($(t))) as FieldNo

Autogenerate 1;

Next f

Next t;

Unfortunately, this takes a very long time in my environment (> 1 hour) for approx. 1400 fields involved.

Any guess to make this happen faster? I'd love to have something like sysobjects/syscolumns master tables, that'd give me the info in a jiffy, but also I know I'm soo 90's with my RDBMS thinking:)

Thanks in Advance

Arthur

Re: Fast way to get all table and field names from an app

Wed, 22 Jun 2016 08:48:27 GMT

bump

Re: Fast way to get all table and field names from an app

marcus_sommer — Wed, 22 Jun 2016 11:25:58 GMT

Couldn't you simply use the system-fields like: $Table, $Field and $Rows to get your wished output. There is within qlikview an own object called Structure-Table - I'm not sure if such an object is available in sense but if not you could create your own table.

- Marcus

Re: Fast way to get all table and field names from an app

Wed, 22 Jun 2016 14:56:17 GMT

Hi Marcus,

thank you, it works, if I create a visualization and then export it as xlsx.

There is no way (in Qlik Sense) to get a hold on $Table and $Field directly, like:

// doesn't work!

systable:

LOAD *

FROM $Table;

STORE systable INTO 'systable.csv' (txt);

I'm not happy with this workaround, but that is another topic...

F.ex. There are problems like these:

System Fields filtering cannot be undone

I filtered once, now I cannot unfilter: So I throw the app away and start again...

Cheers, Arthur

Re: Fast way to get all table and field names from an app

marcus_sommer — Wed, 22 Jun 2016 15:38:19 GMT

Yes you couldn't load these fields because there is no table which contains these kind of data - qlik stored the data within data- and symbol-tables, see: Symbol Tables and Bit-Stuffed Pointers and the system-fields contain a certain aggregation from them.

The selections-issue is not quite clear for me - that they are not visible in the selection-panel is normal - the automatic system-fields and also the as system-fields declared fields (per HidePrefix) are per design not displayed. But if put all these fields on the sheet you could deselect them like in all other fields. And even if there is any selection on those fields it won't have an effect on your other selections and data.

- Marcus

Re: Fast way to get all table and field names from an app

Amit — Tue, 22 Aug 2023 15:54:18 GMT

Dear Arthur. Your solution is brilliant! It's better than anything else I have found. I tried it and it ran in under 1 minute for my data model of 109 tables and 3330 fields. I am running on a standard QV server. I can set up a QMC job to run at the weekend and produce a meta data QVD of all the the tables and columsn in my data model. Thank you so much! Regards. Amit