topic Count of records in QlikView

Count of records

Wed, 20 Jan 2016 20:35:06 GMT

Hi,

I'm trying to list the name of table and number of records from qvds.

And I got it right for one qvd but unable to do it when I have multiple qvds.

Can someone suggest me a logic or sample code for this.

Note: There may be multiple qvds and all of them in different locations.

Regards,

Sachin

Re: Count of records

Anonymous — Wed, 20 Jan 2016 20:45:29 GMT

Taking what you did in the script, this should work. Change Cars.qvd and Emp.qvd to the file path

Let vQVDName = QvdTableName ('Cars.qvd');

Let vNumRows = QvdNoOfRecords('Cars.qvd');

Let vQVDName2 = QvdTableName ('Emp.qvd');

Let vNumRows2 = QvdNoOfRecords('Emp.qvd');

[Rec Count]:

LOAD * INLINE [

QVD Table Name, No Of Records

$(vQVDName), $(vNumRows)

$(vQVDName2), $(vNumRows2)

];

Re: Count of records

petter — Wed, 20 Jan 2016 20:48:07 GMT

This is a simple and straightforward way (Quick and Dirty) of doing it:

Let vQVDName1 = QvdTableName  ('Cars.qvd');
Let vNumRows1 = QvdNoOfRecords('Cars.qvd');
Let vQVDName2 = QvdTableName  ('Emp.qvd');
Let vNumRows2 = QvdNoOfRecords('Emp.qvd');


[Rec Count]:
LOAD * INLINE [
    QVD Table Name, No Of Records
    $(vQVDName1), $(vNumRows1)
    $(vQVDName2), $(vNumRows2)
];

Re: Count of records

Wed, 20 Jan 2016 20:49:41 GMT

Thanks Petter, but is there a way to automate it using a for loop or something like that.

Re: Count of records

swuehl — Wed, 20 Jan 2016 20:51:03 GMT

For each vQVD in FileList ('*.qvd')

[Rec Count]:
LOAD 
 '$(vQVD)' as [QVD Table Name],
 QvdNoOfRecords('$(vQVD)') as [No Of Records]
Autogenerate 1;

Next vQVD

Re: Count of records

sunny_talwar — Wed, 20 Jan 2016 20:57:28 GMT

May be this:

Let vPathName = 'C:\Users\sunny.a.talwar\Downloads\Test\';

For Each File in FileList('$(vPathName)'&'*.qvd')

Let vQVDName = QvdTableName('$(File)');

Let vNumRows = QvdNoOfRecords('$(File)');

[Rec Count]:

LOAD * INLINE [

QVD Table Name, No Of Records

$(vQVDName), $(vNumRows)

];

NEXT File

Re: Count of records

petter — Wed, 20 Jan 2016 20:57:38 GMT

Yes - swuehl just illustrated it ... good luck

Re: Count of records

Wed, 20 Jan 2016 21:08:02 GMT

Hi Sunny,

As usual appreciate thanks for the quick response.

But the issue here is some qvds are in sub folders as well like:

C:\Users\sunny.a.talwar\Downloads\Test\

C:\Users\sunny.a.talwar\Downloads\Test\Sub\

Any idea how to handle this scenario

Re: Count of records

Wed, 20 Jan 2016 21:11:56 GMT

Hi Stefan,

But my qvds are in different folders. Any idea how to handle this scenario.

Re: Count of records

Wed, 20 Jan 2016 21:13:47 GMT

Hi Pavan,

But I have n qvd's and all of them are in different locations.

Re: Count of records

swuehl — Wed, 20 Jan 2016 21:14:06 GMT

Just have a look at FOR EACH ... NEXT in the HELP

http://help.qlik.com/de-DE/qlikview/12.0/Subsystems/Client/Content/Scripting/ScriptControlStatements/For%20Each.htm?q=For each

There is a code sample for scanning all subfolders as well.

Re: Count of records

sunny_talwar — Wed, 20 Jan 2016 21:26:37 GMT

Something like this using the help section as Stefan pointed out:

SUB DoDir(Root)

  For Each File in FileList(Root & '\*.qvd')

  Let vQVDName = QvdTableName('$(File)');
  Let vNumRows = QvdNoOfRecords('$(File)');

  [Rec Count]:
  LOAD *,
    RowNo() as Key;
  LOAD * INLINE [
      QVD Table Name, No Of Records
      $(vQVDName), $(vNumRows)
  ];

  NEXT File

  FOR Each Dir in DirList(Root&'\*')

  Call DoDir(Dir)

  NEXT Dir


END SUB


CALL DoDir ('C:\Users\sunny.a.talwar\Downloads\Test')

Re: Count of records

Tyler_Waterfall — Wed, 20 Jan 2016 21:38:27 GMT

You could also use the Governance Dashboard - that will load all the QVDs in specific folders (and subfolders) and present metadata about QVDs: table name, fields, rows, total values, cardinality, etc.

The 2.0 Governance Dashboard (in beta now) only requires you to save the app and update the configuration page to point to your parent folder(s) where the QVDs are.

You can find the app and some Q&A here: QlikView Governance Dashboard 2.0 App Beta

Re: Count of records

Thu, 21 Jan 2016 07:07:47 GMT

Helpful