topic Re: i have 50 table in SQL server data base we are doing daily manually count in QlikView

i have 50 table in SQL server data base we are doing daily manually count

dolly777 — Thu, 03 Apr 2014 05:21:48 GMT

i have 50 table in SQL server data base we are doing daily manually count but i dont want take like that.in qlikview table name and count and SQl table name and count display in qlikview in directly. we want create one qvd in that qvd field names are "table name" and "count of records"How to write a query.

Hi All,

Any one can help in this query?

Re: i have 50 table in SQL server data base we are doing daily manually count

sujeetsingh — Thu, 03 Apr 2014 05:43:20 GMT

just write a sql script and load it in a qlikview table

Re: i have 50 table in SQL server data base we are doing daily manually count

cwolf — Thu, 03 Apr 2014 06:01:24 GMT

Hi Nirmala,

TTABLE:

select

TABLE_NAME as ALL_TABLES

from information_schema.TABLES where TABLE_SCHEMA='YOUR_SCHEMA_NAME' and TABLE_TYPE='BASE TABLE';

for n = 1 to fieldvaluecount('ALL_TABLES');

let Table = fieldvalue('ALL_TABLES',$(n));

TABLE_NOR:

load

'$(Table)' as TABLE_NAME,

NUNMBER_OF_RECORDS;

select count(*) as NUNMBER_OF_RECORDS from $(Table);

drop table TTABLE;

Regards

Christian

Re: i have 50 table in SQL server data base we are doing daily manually count

cwolf — Thu, 03 Apr 2014 06:32:00 GMT

Hi Nirmala,

if you have load your tables to QlikView Qvds, than you can also read the metadata of the qvd's to get the record numbers:

for each File in filelist ('Your_Directory\*.qvd')

QvdTableHeader:

LOAD

QvBuildNo,

CreatorDoc,

CreateUtcTime,

SourceCreateUtcTime,

SourceFileUtcTime,

SourceFileSize,

StaleUtcTime,

TableName,

Compression,

RecordByteSize,

NoOfRecords,

Offset,

Length,

Comment

FROM $(File) (XmlSimple, Table is [QvdTableHeader]);

Regards

Christian

Re: i have 50 table in SQL server data base we are doing daily manually count

dolly777 — Thu, 03 Apr 2014 06:46:35 GMT

Hi Christian,

tried it...i have confused if u dnt mind share ur mail id based on tht i send clear details

Re: i have 50 table in SQL server data base we are doing daily manually count

cwolf — Thu, 03 Apr 2014 07:14:07 GMT

Please accept my request to be your friend, then you see my email address.

Re: i have 50 table in SQL server data base we are doing daily manually count

Thu, 03 Apr 2014 07:46:02 GMT

Hi Christian,

thanks for the reply

here what is these

information_schema.TABLES where TABLE_SCHEMA='YOUR_SCHEMA_NAME' and TABLE_TYPE='BASE TABLE';

Re: i have 50 table in SQL server data base we are doing daily manually count

cwolf — Thu, 03 Apr 2014 08:12:14 GMT

Hi,

the information_schema is the data dictionary of SQL Server. Have a look at

INFORMATION_SCHEMA.TABLES tutorial and example

If you don't like to use it, you can also create an inline table with your table names:

TTABLE:

LOAD * INLINE [

ALL_TABLES

'Table1' 'Table2' ...

];

for ... next

Re: i have 50 table in SQL server data base we are doing daily manually count

Fri, 04 Apr 2014 07:25:54 GMT

Hi Christian,

I used Inline function..Its working ..Thanks a lot..

Regards

Abinav