topic Don't store a table if no data in QlikView

Don't store a table if no data

Anonymous — Fri, 04 Sep 2015 05:50:07 GMT

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

Re: Don't store a table if no data

jonathandienst — Fri, 04 Sep 2015 05:54:59 GMT

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.

Re: Don't store a table if no data

tresB — Fri, 04 Sep 2015 05:56:25 GMT

filesize( )

Re: Don't store a table if no data

Anonymous — Fri, 04 Sep 2015 06:01:43 GMT

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') >