TableName Function

[marco_puccetti]

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

[marcus_sommer]

This worked:

// drop tables from generators

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

    let vTable = tablename($(i));

    drop tables [$(vTable)];

next

let i             = null();

let vTable   = null();

- Marcus

[marco_puccetti]

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

Thanks

Marco

[jonathandienst]

Hi

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

[marcus_sommer]

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

[marco_puccetti]

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

[jonathandienst]

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

[marco_puccetti]

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

Marco

[marcus_sommer]

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

[marcus_sommer]

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));

            next

        next

        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;

    next

next

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