Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qliktech_uk
Contributor III
Contributor III

QVD Load Stats

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 ;

1 Solution

Accepted Solutions
felipedl
Partner - Specialist III
Partner - Specialist III

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

View solution in original post

12 Replies
vishsaggi
Champion III
Champion III

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

its_anandrjs

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 ;

felipedl
Partner - Specialist III
Partner - Specialist III

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

its_anandrjs

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

qliktech_uk
Contributor III
Contributor III
Author

Thanks Anand.

qliktech_uk
Contributor III
Contributor III
Author

Thanks Felipe, it worked!

qliktech_uk
Contributor III
Contributor III
Author

Additional column.PNG

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?

felipedl
Partner - Specialist III
Partner - Specialist III

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.

qliktech_uk
Contributor III
Contributor III
Author

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;