find

[raadwiptec]

I have a table name list. I would like to go through the qvs scripts and find whether these tables are used in the individual qvs scripts if yes give me the qvs script filename - the file name exist with in the script as filebasename

[madhumitha]

Hello,

please find below script. the below QVSlist table should give you the table name associated qvs and their count.

QVSlist:
load '' as QVSName,
'' as Tablelist,
'' as Count
AutoGenerate 1;

Tablelist:
LOAD
TableName
FROM
Tablelist.xlsx (ooxml, no labels, table is Sheet1);


SUB DoDir (ROOT)

FOR each File in filelist( ROOT & '\*.qvs')

TRACE $(File);

QVS:
LOAD 
filebasename as Tablelist
FROM [$(File)];
inner join
load * resident Tablelist;



load '$(File)' as QVSName,
Tablelist as TableFound,
count(TableFound) as Count
resident
QVS
group by QVSName,TableFound ;


NEXT File

END SUB

CALL DoDir('directory path')

[raadwiptec]

hi Madhu

If there is no filebase name .. for ex

lets imagine if there is a load statement like

select

Invoice number,

invoice name

...

from invoice.xls

here invoice.xls is also table.. (table extracted from oracle database and placed as invoice.xls)

how can i retrieve details in such a situation?

[madhumitha]

if those excels are placed in a directory, you still can use FileBaseName() function and load just the file names it into another table.

If those table are already in a qvw, then you can make use of system tables to get the list of the names.