Search data load scripts in all apps for specific table names

[mikegrattan]

I would like to know if there is a way to search for key words, such as a source table name, in all data load scripts across all apps.  I'm trying to determine which apps use specific tables from our data warehouse in order to clean up the tables that are not being used by any apps.

[abhijitnalekar]

Hi @mikegrattan ,

You can load all your QVD in one application. 

but while loading the QVD select xml from the type.

After loading the QVD.

you can select the table name from the field list

you can load all the QVD which have been generated from all the apps .  and get the table list.

[mikegrattan]

Abhijit,

Thanks for the reply, but my requirement is to search all data load scripts for a table or tables with specific names and specific data sources; these are ODBC connections and not QVD files.

Example:

I would like to search data load scripts across all applications for the CCITEMMAST table and get a list of which applications are using that table in a data load.

[abelmottac]

Hi @mikegrattan

I'm with a similar need that you raise.

Have you got yourself any solution for your problem?

Thanks
Abel

[mikegrattan]

Hello @abelmottac .  No, I have not received a solution to this question.  If this capability is not currently in Qlik I am hoping that Qlik will see this post and consider it to be an enhancement request.

[abelmottac]

Thanks, Mike, for your answer.

I think the same, this would be an enhancement 

[curiousfellow]

Searching for the same.  So I vote for  adding this feature

[BeeGees]

Working with Qlik You have to be creative 😉 As it lacks a lot of administering options (sadly).

First I've created txt file in some folder connection location (lets say lib://Scripts).

It contains those lines:

---

LET vAppId = DocumentName();
LET vAppName = DocumentTitle();

Trace APP_ID|$(vAppId)|APP_NAME|$(vAppName);

This is the script I use in ALL of my application in Qlik instalations (ETLs, CALCs, visialisation APPS, etc). So You have to inject it at the beginning of each APP:

$(Include=[lib://Scripts/AppNameTrace.txt]);

Then I've made special appliacation when I keep track of those things. It loads all the script load log files:

for each FoundFile in filelist( 'lib://LogsFolder' & '\Script\*.log' )

if $(vMinFileDate) < FileTime('$(FoundFile)') then

trace $(FoundFile);

FileList_TMP:

Load distinct
  Text(left(Right('$(FoundFile)', len('$(FoundFile)') -index('$(FoundFile)','/',-1)),36)) as App_ID,
  if(upper(@1) like '*STORE*','STORE','READ') as QVD_ACCESS_TYPE,
  Replace(@1,'/','\') as QVD_FILES,
  FileTime('$(FoundFile)') as ReloadTime

FROM '$(FoundFile)'

(txt, utf8, no labels, delimiter is '\t', msq)

where Upper(@1) like '*.QVD*';


AppNames_TMP:
Load distinct
  Text(SubField(@1, '|', 2)) as App_ID,
  SubField(@1, '|', 4) as App_NAME
  
FROM '$(FoundFile)'

(txt, utf8, no labels, delimiter is '\t', msq)

where 
	Upper(@1) like '*APP_NAME*' and 
    not Upper(@1) like '*TRACE*' and 
    not Upper(@1) like '*UPPER*';

end if;

next FoundFile;

Keep in mind that this is only for very recent scripts. If You want full history You have to also repeat above for each archive logs folder for each node in Your environment.

Last but not least some formatting for better App & QVDs names:

FILE_QVD_TABLE:
NoConcatenate
load 
	App_ID,
    QVD_ACCESS_TYPE,
    replace(replace(left(Right(QVD_FILES,len(QVD_FILES)-Index(QVD_FILES,'\',-1)),len(Right(QVD_FILES,len(QVD_FILES)-Index(QVD_FILES,'\',-1)))-1), 'qvd] (qvd','qvd'), 'qvd](qvd','qvd') as QVD_FILE,
    Date(floor(max(ReloadTime))) as LAST_RELOAD
Resident 
	FileList_TMP
where 
    upper(replace(left(Right(QVD_FILES,len(QVD_FILES)-Index(QVD_FILES,'\',-1)),len(Right(QVD_FILES,len(QVD_FILES)-Index(QVD_FILES,'\',-1)))-1), 'qvd] (qvd','qvd')) like '*QVD'
    and not replace(left(Right(QVD_FILES,len(QVD_FILES)-Index(QVD_FILES,'\',-1)),len(Right(QVD_FILES,len(QVD_FILES)-Index(QVD_FILES,'\',-1)))-1), 'qvd] (qvd','qvd') like '*''*'
group by 
	App_ID,
    QVD_ACCESS_TYPE,
    replace(replace(left(Right(QVD_FILES,len(QVD_FILES)-Index(QVD_FILES,'\',-1)),len(Right(QVD_FILES,len(QVD_FILES)-Index(QVD_FILES,'\',-1)))-1), 'qvd] (qvd','qvd'), 'qvd](qvd','qvd');

join (FILE_QVD_TABLE)
Load 
	distinct 
    App_ID,
    App_NAME
RESIDENT AppNames_TMP
WHERE not App_NAME like '*''*';

Drop Table FileList_TMP, AppNames_TMP;

I'll leave visualisations for Yourself.