Command to check if a QVD file exists

[#1]

My QVD files holding transactions are stored per year to be able to select data sets more easily in our QV applications.

I want to perform an incremental reload on my transaction data. This is a sequential file holding a record number but highest written record number is not necessarily in the highest year.

I have a transactions_2010.qvd, transactions_2009.qvd

When performing the reload (in a loop for x number of years), I first load the data from the existing QVD file, calculate the highest record number found in the QVD file and then load the data from the transaction file for the apprioprate year and only records > highest record number in the QVD file.

This works very good and boosts reload times.

However, if the QVD file does not exist yet, the script fails as the read from the QVD file ends up in error (file not found).

Is there a way in QV to check if the QVD file exists ? If it does not exist for the given year, I would perform a full load for the given year.

So my script would be like :

If '$(vInputIncremental)' = 'yes' then

transactions:
LOAD
*
FROM
transactions_$(vYear).qvd (qvd);

tt_max_record_number:
LOAD
max(record_number) as record_number_max
RESIDENT transactions;

let vLastrecord = peek('record_number_max', 0);
DROP TABLE tt_max_record_number;

else

let vLastrecord = 0; // If no incremental reload, all records need to be read = PSRRNO > 0

end if

LOAD * ;

SQL SELECT... ;

The first condition 'If '$(vInputIncremental)' = 'yes' then' should be expanded with 'and transactions_$(vYear).qvd exists' but how do you write this in QV ?

[#2 · Accepted solution]

Hi,
try QvdCreateTime function.
If file don't exists, returns NULL.

[#3]

Thanks a lot. I tried to integrate it, but I get an error message on the following script line (I already translated my variables to make it more readible) :

If 'yes' = 'yes' and not(isnull(qvdcreatetime(..\QVD\Accounting\general_ledger_A1_2010.qvd))) then

The '..\QVD\Accounting\' comes from a variable that is read from an 'include' file that holds the location of our different QVD files.

The error message says :

Error in expression:
')' expected
If 'yes' = 'yes' and not(isnull(qvdcreatetime(..\QVD\Accounting\general_ledger_A1_2010.qvd))) then

When we load data from our QVD files, the same relative path name is used.

An alternative script line ends up with the same error message (I have left the variable in the expression, but same translation is used as above :

If isnull(qvdcreatetime($(accountingdir)general_ledger_$(vASW_Company)_$(vYear).qvd)) = 0 then

[#4]

Hi,
use
qvdcreatetime('$(accountingdir)general_ledger_$(vASW_Company)_$(vYear).qvd')
instead of
qvdcreatetime($(accountingdir)general_ledger_$(vASW_Company)_$(vYear).qvd)

Alex

[#5]

Thanks a lot. That happened to do the trick.

[heij1421]

Hi,

When I use the following statement it works fine:

LET vListQVDExistsProjectactuals = NOT isnull(QVDCreateTime('D:\Data\123_Projectactuals.qvd'));
IF $(vListQVDExistsProjectactuals) THEN ..

However there are multiple files ending with Projectactuals, like 456_Projectactuals etc.

I want to check if a file ending with *_Projectfiles exist. How do I do that.

I have tried  LET vListQVDExistsProjectactuals = NOT isnull(QVDCreateTime('D:\Data\*_Projectactuals.qvd'));

but that doesn't work as the variable probably wants to have 1 unique file name.