topic Re: Sub for reading File()-Data from Table in QlikView

Sub for reading File()-Data from Table

GeorgeQV — Thu, 04 Feb 2016 13:59:31 GMT

Dear all,

I would like to use the File() functions for extracting data about the table's source like Path, Extension and last date of change. However, most of the functions only work in the current LOAD statement.

Example:

Load

FileTime() as FileTime,

FileName() as FileName

From

\..\file.txt;

Now it would be great to have a reusable sub which can read these attributes from every source. But then I have to leave the current LOAD statement and the File() functions mostly return NULL. I tried using a preceding LOAD:

Example:

Load *,

FileTime() as FileTime,

FileName() as FileName;

Load

From

\..\file.txt;

And putting this into a subroutine:

Example:

Sub GetFileData

Load *,

FileTime() as FileTime,

FileName() as FileName;

End Sub

Call GetFileData;

Load

From

\..\file.txt;

This will add the File() fields to every row of the table. But I would like to create another table or read this data into variables. The problem is still: Everything is external from the current LOAD and the File() functions return NULL. Also, I only need one row of this data, not for all lines of the table. Does anybody know a way to do it? I just want to know where my data comes from, when it was modified, the name of the txt-file etc.

Thank you in advance!

Re: Sub for reading File()-Data from Table

settu_periasamy — Fri, 05 Feb 2016 02:58:58 GMT

Hi,

May be try to use Distinct in your subroutine and give the TableName(Here : FileList) also.. Like

Sub GetFileData

FileList:
Load Distinct FileTime() as FileTime,
FileName() as FileName;
End Sub

Call GetFileData;

Table1:
LOAD A,
B
FROM
SourceFile1;

Call GetFileData;
LOAD A,
B
FROM
SourceFile2;

Re: Sub for reading File()-Data from Table

jonathandienst — Fri, 05 Feb 2016 04:44:49 GMT

The preceding load works on the output of the LOAD below, so none of these options (apart from your original script snippet) will work. To read only the attributes, use something like:

Attribs:

First 1 LOAD

FileTime() As FileTime,

FileName() As FileName

FROM C:\Data\*.xls

(....)

You can Peek() these values into variables as required.

Re: Sub for reading File()-Data from Table

GeorgeQV — Fri, 26 Feb 2016 09:47:22 GMT

Thank you Jonathan, the statement FIRST 1 LOAD and the advise to read the same file again led me to the following subroutine. You just need to save the Table Name and the Source File Path to open the connection once again and load one single record with the file attributes:

SUB GetFileData(vL.TableName, vL.SourceFilePath)

    IF NoOfRows('FileData') > 0 THEN
    
        Concatenate (FileData)
        
        FileData:
        
        First 1 LOAD
        
            '$(vL.TableName)' as TableName,
        
             FileTime() As FileTime,
        
             FileName() As FileName
        
        FROM $(vL.SourceFilePath);
        
    ELSE
        
        FileData:
        
        First 1 LOAD
        
             '$(vL.TableName)' as TableName,
        
             FileTime() As FileTime,
        
             FileName() As FileName
        
        FROM $(vL.SourceFilePath);
    
    ENDIF

END SUB