topic Re: QVD Load Stats in QlikView

QVD Load Stats

qliktech_uk — Mon, 21 Aug 2017 19:02:30 GMT

Hi,

I am using the code below to get the stats, it comes up as syntax error, I am unable to figure out why, your help is much appreciated.

SUB Stats;

LET vEndTime = Now();

LET vRecords = NoOfRows('$(vTable)');

LET vFields = NoOfFields('$(vTable)');

Statistics:

LOAD * From Statistics.QVD; //where TableName <> '$(vTable)';

Join (Statistics)

LOAD

DISTINCT

‘$(vTable)’ AS TableName,

‘$(vStartTime)’ AS StartTime,

‘$(vEndTime)’ AS EndTime,

'$(vRecords)' AS Records,

'$(vFields)' as Fields

RESIDENT Statistics;

STORE Statistics INTO Statistics.qvd;

DROP TABLE Statistics;

End SUB ;

Re: QVD Load Stats

vishsaggi — Mon, 21 Aug 2017 19:19:19 GMT

What is the error you are getting can you post screen shot of that error?

Re: QVD Load Stats

its_anandrjs — Mon, 21 Aug 2017 19:26:08 GMT

It seems correct, remove Termination symbol after Stats and try

SUB Stats

LET vEndTime = Now();

LET vRecords = NoOfRows('$(vTable)');

LET vFields = NoOfFields('$(vTable)');

Statistics:

LOAD * From Statistics.QVD; //where TableName <> '$(vTable)';

Join (Statistics)

LOAD

DISTINCT

‘$(vTable)’ AS TableName,

‘$(vStartTime)’ AS StartTime,

‘$(vEndTime)’ AS EndTime,

'$(vRecords)' AS Records,

'$(vFields)' as Fields

RESIDENT Statistics;

STORE Statistics INTO Statistics.qvd;

DROP TABLE Statistics;

End SUB ;

Re: QVD Load Stats

felipedl — Mon, 21 Aug 2017 19:33:05 GMT

Hi,

Your quotation marks are strange

the first 3 lines

‘$(vTable)’ AS TableName,

‘$(vStartTime)’ AS StartTime,

‘$(vEndTime)’ AS EndTime,

Have different quotation marks as to

'$(vRecords)' AS Records,

'$(vFields)' as Fields

Try replacing the second load with this:

LOAD

DISTINCT

'$(vTable)' AS TableName,

'$(vStartTime)' AS StartTime,

'$(vEndTime)' AS EndTime,

'$(vRecords)' AS Records,

'$(vFields)' as Fields

RESIDENT Statistics;

Felipe

Re: QVD Load Stats

its_anandrjs — Mon, 21 Aug 2017 19:34:48 GMT

See the attached sample subroutine you can apply this way in your table

Fact:
Load * Inline [
KeyNames
A_Key
B_Key
C_Key
];

LET vTable = 'Fact';

SUB Stats
LET vEndTime = Now();
LET vRecords = NoOfRows('$(vTable)');
LET vFields = NoOfFields('$(vTable)');

Statistics:
Load * Inline
[
ColumnA
‘$(vEndTime)’
'$(vRecords)'
'$(vFields)'
];

STORE Statistics INTO Statistics.qvd;
//DROP TABLE Statistics;

End SUB;

//Calling Subroutine Here
CALL Stats

Re: QVD Load Stats

qliktech_uk — Mon, 21 Aug 2017 19:57:41 GMT

Thanks Anand.

Re: QVD Load Stats

qliktech_uk — Mon, 21 Aug 2017 19:57:58 GMT

Thanks Felipe, it worked!

Re: QVD Load Stats

qliktech_uk — Mon, 21 Aug 2017 20:04:51 GMT

After I load the data, I get an additional column (column 1) which is not part of the table structure, how is it inserted and how can I delete it?

Re: QVD Load Stats

felipedl — Mon, 21 Aug 2017 20:11:38 GMT

I would assume its coming from this

// Probably a field in there that is loading the 1st column and as you join its perpetuated

LOAD * From Statistics.QVD; //where TableName <> '$(vTable)';

Join (Statistics)

Just comment the above lines and you'll be good to go.

Felipe.

Re: QVD Load Stats

qliktech_uk — Mon, 21 Aug 2017 20:22:50 GMT

This is the script for Statistics, there is nothing else that I am adding to the table.

Statistics:

LOAD * Inline

[TableName, Source, ReloadedDate, StartTime, EndTime, Duration, Records, Fields];

Store Statistics into Statistics.QVD;

DROP Table Statistics;

Re: QVD Load Stats

felipedl — Mon, 21 Aug 2017 20:53:41 GMT

If you only need the stats for each QVD you have,

I've done a quick example on how to do it if you don't have all the tables loads preaviously.

You just have to change the directory of the folder, and it will get all the QVDs on that particular folder and get the stats.

If you don't put anyhtin in the vDirFile variable, it will get the QVDs from the same path as the QVW.

Felipe.

Re: QVD Load Stats

qliktech_uk — Mon, 21 Aug 2017 22:42:28 GMT

Thanks so much Felipe.

Re: QVD Load Stats

felipedl — Tue, 22 Aug 2017 13:21:20 GMT

No problem, glad to help