topic Re: Check if table exists in Management & Governance

Check if table exists

micheledenardi — Mon, 26 Feb 2018 10:59:40 GMT

Hi guys,

i've a question regarding the topic "Check if table exists".

In a normal load I'm using the following code, and it's works fine:

MyTable:
Load *
From MyQvd.qvd(qvd);

If NoOfRows('MyTable') > 0 Then
     Trace MyTable has at least one record
End if

but now I have a different situation.

I've got one qvd per day (MyQvd_2018-01-01.qvd, MyQvd_2018-01-02.qvd, MyQvd_2018-01-03.qvd and so on...) so I've setup a load like:

MyTable:
Load *
From MyQvd_*.qvd(qvd);

How can i check if each qvd has records or not by using this load (with the * on the qvd name) in a short way? Because there are some cases where the qvd exists without record and i want to find all the cases.

I would like to avoid "for... next" loops...

Thanks

Re: Check if table exists

juraj_misina — Mon, 26 Feb 2018 12:03:17 GMT

Hi,

you can use QvdNoOfRecords() function (https://help.qlik.com/en-US/sense/February2018/Subsystems/Hub/Content/Scripting/FileFunctions/QvdNoOfRecords.htm), perhpas like this (did not test this):

RecordCheck:
LOAD
     QvdFilePath,
     QvdNoOfRecords(QvdFilePath)     as QvdRecordCount,
     QvdCreateTime(QvdFilePath)     as QvdCreateTime
;
LOAD
     FilePath()     as QvdFilePath
From My_QVD*.qvd(qvd);

Hope this helps

Juraj

Re: Check if table exists

micheledenardi — Mon, 26 Feb 2018 12:37:06 GMT

Hi Juraj,

your solution doesn't work because if i have an empty qvd (without any record) the table is not loaded so QvdNoOfRecords() function is not calculated.

Re: Check if table exists

juraj_misina — Mon, 26 Feb 2018 12:54:07 GMT

Hi Michele,

then your only option seems to be a for loop.

For Each vFile in FileList ('QVD*.qvd')

RecordCheck:
LOAD
    '$(vFile)'                    as QvdFilePath,
    QvdNoOfRecords('$(vFile)')    as QvdRecordCount,
    QvdCreateTime('$(vFile)')    as QvdCreateTime  
AutoGenerate 1; 

NEXT vFile

However, this will be quite fast, because it will not read any data (only file metadata) and you can associate it with fact table through QvdFilePath for example.