Listing QVD and File Extensions in Script

[tedalien]

Hello Community,

I have been searching this topic and di not find something for my case, although some similar are available

but different enough to not be able to find a solution. Hence I thought asking here...

Goal to Achieve:

• List of table loaded
• List of extensions of files loaded

I know that $Table can get the list of tables loaded, however this piovide the list of the final transformed tables and I am trying to get the list of the Data Sources for an application. i.e. if I have two tables joined I will see only one but I need both being listed and all their extensions as well.

I could enter in each Table the File* functions like below:

[My Table 1]

LOAD

[%KEY] as [%My KEY],
[My Field 1],
filename( ) as [My File Name],
FilePath() as [My File Path],
FileExtension() as [My File Extension]
FROM path/My_Table.QVD (qvd) ;

However I'd like finding a solution which I can simply add the application's script without amending every each one table.

I thought then to use a loop like the below:

For vTable = 0 to NoOfTables()-1
LET vTableName = TableName($(vTable)) ; 

[Data Sources]:

Load '$(vTableName)' as [Table Name],
'$(vTable)' as [Table Nr],
filename() as [Filename]
AutoGenerate 1;
next vTable

So far for both Fields [Table Name] and [Table Nr] it works, but I seems not getting able to use the filename() being looped to trouhg the tables. What I have already tried is also filename('$(vTableName)') and filename('$(vTable)'') but it did not go well.

I would use the same logic to get the FileExtension working on a loop.

Would you able to help me please?

Many Thanks in Advance!

Best Regards,

Alen

[ashfaq_haseeb]

Hi,

Check if this helps

http://community.qlik.com/message/532016#532016

Regards

ASHFAQ

[simondachstr]

This is what I am using..

FOR Each vFile in filelist ('$(Path)'&'\*.qvd')

  QVD_Files:

  LOAD

  Subfield('$(vFile)','\\',2) AS Name,

  FileSize( '$(vFile)' )/1024 AS Size, //Divided by 1024 to load Size in KB

  FileTime( '$(vFile)' ) AS FileTime,

  QvdNoOfRecords( '$(vFile)' ) AS Rows,

  QvdNoOfFields( '$(vFile)' ) AS Fields

  autogenerate 1;

NEXT vFile

You can modify the above script to read every file and then get the file extension by the FileName using subfield(FileName('$(vFile'),'*',2) AS FileExtension

[tedalien]

Hi Ashfaq

Thanks a lot for your feedback,

unfortuntaley I do intend to keep the solution within the script and not using C+

Regards,

Alen

[tedalien]

Hi Martin,

thank you very much for your feedback.

As I understood the filelist search in the directory for all QVDs in there.

It is an interesting solution but I see two problems with it.

1. the Qvds loaded in the application are coming from different $(Path) and I would need to loop the path to get all directories involved (not huge issue though)

2.  in the different directories we store lot of QVD and Flat files (XLS, txt ,...) but not all of them are loaded in the application, I would need to get only the one which are loaded. Where clause might be of help if names of files and tables' are always the same. Which is not the case. I could use the Tablename to get the file in the directory with the same name, but then I would get only those tables already transformed and whose name correspond to a "file name".ext

Do you know if is possibile to loop on file function?

Regards,

Alen

[rwunderlich]

It sounds like what you are looking for is data lineage info. I can suggest a couple approaches, depending on how you plan to utilize the data.

1. The Governance dashboard scans all your QVWs and QVD and provides lineage data by QVW and QVD. That would allow you to view the sources for any qvw in a single place.

2. If you need to build visible lineage data within each application, you can do it with Qlikview Components (QVC). The call you would insert in your script is:

CALL Qvc.DataLineage

I hadn't tried that routine for a while and note there is a bug. It does not correctly follow the source of nested qvds. What that means is that it will tell you that your qvw loaded customer.qvd, but it does not correctly identify what created customer.qvd. I'll fix that in the next release.

You can get QVC at http://qlikviewcomponents.org.

-Rob

http://masterssummit.com

http://robwunderlich.com

[tedalien]

Hi Rob,

thank you very much for your feedback. From your suggestion I assume that there is not an 'easy' script-trick and the question is probably less trivial than what could look like at first sight.

I will have a look to both the solutions and see if we can implement them for our applications.

Thanks again,

Alen