Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
gottsmann
New Contributor II

Sub for reading File()-Data from Table

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

     A,

     B,

     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

     A,

     B,

From

\..\file.txt;

And putting this into a subroutine:

Example:

Sub GetFileData

Load *,

     FileTime() as FileTime,

     FileName() as FileName;

End Sub

Call GetFileData;

Load

     A,

     B,

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!

1 Solution

Accepted Solutions
MVP
MVP

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

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
3 Replies

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

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;

MVP
MVP

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

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
gottsmann
New Contributor II

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

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

Community Browser