Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi - I have a script that pulls in a QVD containing a year's worth of data, then exports it into individual monthly files. It's a universal scrip that I'm applying to a large amount of different QVDs. Not all QVDs have a full year of data.
The script runs fine as if there is no data for a month in the QVD it creates a table with 0 rows imported, however it then creates an empty QVD file.
An example below - Load table is the possibly yearly dataset, and table 01 is pulling out just the January data. So what can I add in that if there is no Jan data, then don't STORE and just drop the table. Assuming and If Then Else statement, but what's the syntax to put into the If/Then to check the table size ?
LOAD:
Load * from xxxxx.qvd (qvd);
01:
NoConcatenate
Load *
Resident LOAD
where
(DateMonth = 'Jan' and DateYear ='$(Year)');
STORE 01 into $(vQVDDirectory)$(vCube)$(Year)01.qvd;
Drop table 01;
Cheers,
Dave
Hi
T01:
NoConcatenate
Load * Resident LOAD
where (DateMonth = 'Jan' and DateYear ='$(Year)');
If NoOfRows('T01') > 0 Then
STORE T01 into $(vQVDDirectory)$(vCube)$(Year)01.qvd;
Drop table T01;
End If
I changed the table name to avoid the need for delimiters.
Hi
T01:
NoConcatenate
Load * Resident LOAD
where (DateMonth = 'Jan' and DateYear ='$(Year)');
If NoOfRows('T01') > 0 Then
STORE T01 into $(vQVDDirectory)$(vCube)$(Year)01.qvd;
Drop table T01;
End If
I changed the table name to avoid the need for delimiters.
filesize( )
Thanks - I also solved using
If RowNo() = 0 |
then Drop table 01;
ELSE
STORE 01 into $(vQVDDirectory)$(vCube)$(Year)01.qvd;
Drop table 01;
Edit : I've just expanded this script to contain multiple IF statements on multiple tables. For anyone reading this, RowNo() only works on the original table. If you are creating multiple sub tables from the source table, such as in my case with a multiple If Where clauses, RowNo() will run but will always return 0 so doesn't work.
To use on multiple subtables use Jonathan's answer : NoOfRows('T01') >