topic Re: TableName Function in QlikView

TableName Function

marco_puccetti — Mon, 04 May 2015 13:11:25 GMT

I need to use a function in order to delete all the previous data loaded, but if i use this statement:

If (len(TableName('TABLE_NAME')) > 0) Then

DROP TABLE TABLE_NAME;

End If

this seems to not find any table with that name, although there's just the table loaded previously,

This is the same if i use the DROP TABLES function.

If i use only the DROP TABLE function, it crashes and return an error because the table is not found.

So how the data can be persistent if the tables are not visible as objects previously loaded?

Which function have i to use?

Thanks

Marco

Re: TableName Function

marcus_sommer — Mon, 04 May 2015 13:22:44 GMT

This worked:

// drop tables from generators

for i = NoOfTables() - 1 to 0 step - 1

let vTable = tablename($(i));

drop tables [$(vTable)];

let i = null();

let vTable = null();

- Marcus

Re: TableName Function

marco_puccetti — Mon, 04 May 2015 13:27:25 GMT

If i use NoOfTables() at the begin of the script, i get 0 tables.

Thanks

Marco

Re: TableName Function

jonathandienst — Mon, 04 May 2015 13:36:03 GMT

All the tables are dropped at the start of a normal reload, and there is no reason to attempt to explicitly drop them. The tables are then re-created during the reload.

The exception is a partial reload, which requires a load script specifically designed to handle a partial load. Most QV solutions do not require a partial load, but it can be useful for near-real time solutions with large volumes of source data and an incremental load strategy.

HTH

Jonathan

Re: TableName Function

marcus_sommer — Mon, 04 May 2015 13:38:50 GMT

This script-part is nearly the last statement within the entire script aimed to clean up everything what is being left. If you want to drop tables after certain load-statements and it should more generic as "drop table Table;" you will need something like this:

let vLoadTable = TableName(NoOfTables() - 1);

drop tables $(vLoadTable);

- Marcus

Re: TableName Function

marco_puccetti — Mon, 04 May 2015 13:40:05 GMT

So if i need a list of table also out of the reload script i can't have the visibility, though these functions are not valid out of the script editor?

Marco

Re: TableName Function

jonathandienst — Mon, 04 May 2015 13:42:10 GMT

What is it that you would like to do with a list of tables?

Re: TableName Function

marco_puccetti — Mon, 04 May 2015 13:48:09 GMT

I need to do what is the purpose of the function!!

Marco

Re: TableName Function

marcus_sommer — Mon, 04 May 2015 13:49:38 GMT

You want to know which tables are available within a qvw and/or which data-sources are included then you will need to read the meta-data:

Governance Materials.zip

QlikView Governance Dashboard 1.0.

- Marcus

Re: TableName Function

marcus_sommer — Mon, 04 May 2015 13:58:15 GMT

Both examples from me have a useful purpose and you could it extend to more complex things like:

let vTabName = 'DataStructures';

let vLoadStart = num(now() - today(), '#.########', '.', ',');

for i = 0 to NoOfTables() - 1

for ii = 1 to NoOfFields(TableName($(i)))

let vTableName = '[' & TableName($(i)) & ']';

let vFieldName = '[' & FieldName($(ii), TableName($(i))) & ']';

let vFieldCounter = 0;

for j = 0 to NoOfTables() - 1

for jj = 1 to NoOfFields(TableName($(j)))

let vFieldNameCounter = '[' & FieldName($(jj), TableName($(j))) & ']';

let vFieldCounter = if('$(vFieldName)' = '$(vFieldNameCounter)', 1 + $(vFieldCounter), $(vFieldCounter));

DataStructure:

Load

$(i) as TableNumber,

TableName($(i)) as TableName,

NoOfRows(TableName($(i))) as Rows,

$(ii) as FieldNumber,

FieldName($(ii), TableName($(i))) as FieldName,

Count($(vFieldName)) as FieldTotalCount,

Count(distinct $(vFieldName)) as FieldDistinctCount,

MissingCount($(vFieldName)) as FieldMissingCount,

TextCount($(vFieldName)) as FieldTextCount,

NumericCount($(vFieldName)) as FieldNumericCount,

NullCount($(vFieldName)) as FieldNullCount,

num(Count($(vFieldName)) / NoOfRows(TableName($(i))), '#.##0%') as FieldDensity,

if(TextCount($(vFieldName)) >= 1 and NumericCount($(vFieldName)) >= 1, 'mixed',

if(TextCount($(vFieldName)) >= 1, 'string', 'numeric')) as FieldFormat,

if($(vFieldCounter) > 1, 'yes (' & $(vFieldCounter) & ')', 'no') as Key

Resident $(vTableName) Where '$(vTableName)' <> 'DataStructure';

let vFieldCounter = 0;

store DataStructure into $(pApp@03)DataStructure.qvd (qvd);

$(Include=$(pScript@04)Include_LoadLogging.txt);

But with bigger apps it will be rather slow but many things are possible.

- Marcus