Take record counts of tables

[Anonymous]

Hello all,

I have a requirement like below.

In a database I have tables almost 20 and i want to display records count in dashboard.

Like

select count(*) from dim_orders

Result 200

In Qlikview

Orders: 200

like way I have to display for all tables.

Please help me how to proceed on this

Regards

Kumar

[Anonymous]

Hi Chanty,

here before start this script I have to create ODBC script to read the tables from data base.

Suppose If I have all QVD's available in any mounted folder, then how do I start. Can you explain me.

[MayilVahanan]

Hi

Try like this

T:

Load * Inline

[

FileName, Records

];

FOR Each i in 'ITE.qvd', 'They.qvd', 'Bnd.qvd'

Let vRecords = QvdNoOfRecords('$(i)');

Concatenate(T)

LOAD '$(i)' as FileName, $(vRecords) as Records AutoGenerate 1;

NEXT

[Anonymous]

Hi MAYIL,

Here also we are manually entering QVD names.

I am doing as you suggested, but it is giving error.

Here is the code

LET Vqvdpath = 'D:\QlikView\SourceDocuments\QVD\test\';

T:

Load * Inline

[

FileName, Records

];

FOR Each i in 'dim_account.qvd', 'dim_status.qvd', 'dim_team.qvd'

Let vRecords = QvdNoOfRecords('$(i)');

Concatenate(T)

LOAD '$(i)' as FileName, $(vRecords) as Records AutoGenerate 1;

NEXT

[Anonymous]

I did according to thread which you shared. But it is giving some problem.

ODBC CONNECT TO [QA Server_EOM_Qlikview];

LET Vqvdpath = 'D:\QlikView\SourceDocuments\QA\QVW\';

For i = 0 to NoOfTables()-1 

  LET vTableName = TableName($(i)); 

  For j = 1 To NoOfFields('$(vTableName)') 

  LET vFieldName = FieldName($(j),'$(vTableName)'); 

  TableStats: 

  LOAD 

     '$(vTableName)' as TableName, 

     NoOfRows('$(vTableName)') as RowCount, 

     NoOfFields('$(vTableName)') as FieldCount, 

     '$(vFieldName)' as FieldName, 

     FieldValueCount('$(vFieldName)') as FieldValueCount 

  AUTOGENERATE 1 

  ; 

  Next 

Next 

STORE TableStats INTO $(Vqvdpath)TableStats.qvd (qvd);

[Anonymous]

Can anybody have idea about this issue.

[MayilVahanan]

LET Vqvdpath = 'C:\Users\mramasam\Downloads\';

T:

Load * Inline

[

FileName, Records

];

FOR Each i in 'ITE.qvd', 'They.qvd', 'Bnd.qvd'

Let vRecords = QvdNoOfRecords('$(Vqvdpath)$(i)');

Concatenate(T)

LOAD '$(i)' as FileName, $(vRecords) as Records AutoGenerate 1;

NEXT

[MayilVahanan]

HI

Try like above

[jonathandienst]

If your database are using SQL Server, then use this script to get the table sizes on the database:

    SQL SELECT

        t.Name As [Table],

        s.Name As [Schema],

        p.rows AS Count,

        SUM(a.total_pages) * 8192 AS TotalSpace,

        SUM(a.used_pages)  * 8192 AS UsedSpace ,

        (SUM(a.total_pages) - SUM(a.used_pages)) * 8192 AS UnusedSpace

    FROM

        sys.tables t

    INNER JOIN    

        sys.indexes i ON t.OBJECT_ID = i.object_id

    INNER JOIN

        sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

    INNER JOIN

        sys.allocation_units a ON p.partition_id = a.container_id

    LEFT OUTER JOIN

        sys.schemas s ON t.schema_id = s.schema_id

    WHERE

        t.is_ms_shipped = 0

        AND i.OBJECT_ID > 255

    GROUP BY

        t.Name, s.Name, p.rows

    ;  

This is (a lot) faster than doing a SELECT Count(*) on each table.

[Anonymous]

Thanks Jonathan. My database is now postgre, will it work for that data base.

[Anonymous]

Hi Mayil,

It is working fine if I have QVD's.

Can you help me on how to get the same with database(postgre)