topic Re: Command to check if a QVD file exists in QlikView

Command to check if a QVD file exists

Thu, 27 May 2010 17:08:39 GMT

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 ?

Command to check if a QVD file exists

Thu, 27 May 2010 17:15:47 GMT

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

Command to check if a QVD file exists

Fri, 28 May 2010 13:58:39 GMT

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

Command to check if a QVD file exists

Fri, 28 May 2010 14:38:56 GMT

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

Alex

Command to check if a QVD file exists

Fri, 28 May 2010 15:46:36 GMT

Thanks a lot. That happened to do the trick.

Re: Command to check if a QVD file exists

heij1421 — Wed, 17 Dec 2014 22:40:55 GMT

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.